CODEWITHSUNDEEP
vbaexcel
sc1324
sc1324

Reputation: 600

copy & paste with variable sheet name object required vba

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 & paste data from 7.26.2018 into All Pending, from column A to column N.

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.

Now, I don't know how to set the sheet name to be a variable in order to copy & paste results so I am getting an error of object required for ws3. Any ideas on how to go around it?

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" & LR3).Copy
ws4.Range("A" & LR4 + 1).PasteSpecial Paste:=xlPasteValues
End Sub

Upvotes: 0

Views: 1962

Answers (1)

BigBen
BigBen

Reputation: 49998

Worksheet.Name(Date) is not the syntax you are looking for, since:

  1. You want the Worksheets collection, just like the next line - ... = Worksheets("All Pending").
  2. You've already formatted Date as "M.DD.YYY", so if you want to reference the sheet by its name, you can't just use Date. And you wouldn't use .Name either - again the next line shows you how to reference a sheet by its name - in this case Worksheets(Format(Date, "M.DD.YYYY"))

The easiest way is just to Set ws3 = ActiveSheet at the beginning, since you are also using ActiveSheet to rename the sheet in question.

Note that for variable declaration, Dim ws3, ws4 as Worksheet declares ws3 as Variant - you should have Dim ws3 as Worksheet, ws4 as Worksheet. Same goes for the next line.

So here is a revision of your original code:

Sub Main()
    Dim ws3 As Worksheet, ws4 As Worksheet
    Dim LR3 As Long, LR4 As Long

    Set ws3 = ActiveSheet
    Set ws4 = Worksheets("All Pending")

    ws3.Name = Format(Date, "M.DD.YYYY")
    ws4.Visible = xlSheetVisible ' Not really necessary, as you mentioned

    LR3 = ws3.Cells(Rows.Count, "A").End(xlUp).Row
    LR4 = ws4.Cells(Rows.Count, "A").End(xlUp).Row

    ws3.Range("A2:N" & LR3).Copy
    ws4.Range("A" & LR4 + 1).PasteSpecial Paste:=xlPasteValues
End Sub

Upvotes: 1

Related Questions