title: copy & paste with variable sheet name object required vba
body: <p>I have a report with two sheets of data. One is called 7.26.2018 and another is All Pending. When I open the report, only 7.26.2018 is visible, and I want to have vba codes to copy &amp; paste data from 7.26.2018 into All Pending, from column A to column N.</p>
  
  <p>Each time I run this report, I need change the sheet name to the date I am working on it. So if I work on it tomorrow, I need to change the sheet name to 7.27.2018 and paste the data into All Pending tab. In essence, I am keeping all records in All Pending using the new data from 7.26.2018 sheet.</p>
  
  <p>Now, I don't know how to set the sheet name to be a variable in order to copy &amp; paste results so I am getting an error of <code>object required</code> for <code>ws3</code>. Any ideas on how to go around it?</p>
  
  <pre><code>Sub Main()
  ActiveSheet.Name = Format(Date, "M.DD.YYYY")
  Worksheets("All Pending").Visible = xlSheetVisible
  Dim ws3, ws4 As Worksheet
  Dim LR3, LR4 As Long
  Set ws3 = Worksheet.Name(Date)
  Set ws4 = Worksheets("All Pending")
  LR3 = ws3.Cells(Rows.Count, "A").End(xlUp).Row
  LR4 = ws4.Cells(Rows.Count, "A").End(xlUp).Row
  ws3.Range("A2:N" &amp; LR3).Copy
  ws4.Range("A" &amp; LR4 + 1).PasteSpecial Paste:=xlPasteValues
  End Sub
  </code></pre>