copy & paste with variable sheet name object required vba

Question

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

At the beginning, Set ws3 = ActiveWorksheet? Note that Dim ws3, ws4 as Worksheet declares ws3 as Variant - you need to do Dim ws3 as Worksheet, ws4 as Worksheet. Same for the next line. — BigBen
– BigBen, Commented Jul 26, 2018 at 18:10
I figured I don't need to un-hide the All Pending sheet to do the copying & pasting so I always have the same sheet as active sheet and then do it that way while setting ws3 as activeworksheet. — sc1324
– sc1324, Commented Jul 26, 2018 at 18:18

BigBen · Accepted Answer · 2018-07-26 18:26:56Z

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

You want the Worksheets collection, just like the next line - ... = Worksheets("All Pending").
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

Collectives™ on Stack Overflow

copy & paste with variable sheet name object required vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related