Select method of Worksheet Class Failed Error when opening another workbook

Question

Sub Copy()

Workbooks("Data.xlsm").Sheets("28May2020").Select

Workbooks("Data.xlsm").Sheets("28May2020").Activate

Dim lastrow As Integer

lastrow = ThisWorkbook.Sheets("COPYHERE").Cells(Rows.Count, 2).End(xlUp).Row

Sheets("COPYHERE").Range("A" & lastrow + 1).Value = Date
Sheets("COPYHERE").Range("B" & lastrow + 1).Value = Time

Range("L43:X93").Copy Worksheets("COPYHERE").Range("A" & lastrow + 2)

Sheets("COPYHERE").Select

Timer

End Sub

Sub Timer()

Application.OnTime Now() + TimeValue("00:00:30"), "Copy"

End Sub

Whenever i am opening another workbook my VBA coded sheet shows error. — yatender singh, May 27 '20 at 19:58
It would be better to just [avoid Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, May 27 '20 at 19:59
And [Use `Long`, not `Integer`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). — BigBen, May 27 '20 at 20:00
@BigBen i am a beginner in VBA unable to replace anything... can you please guide ... — yatender singh, May 27 '20 at 20:09

score 0 · Answer 1 · answered May 27 '20 at 23:50

Before Sheets("COPYHERE").Select, place a line ThisWorkbook.Activate. That activates the workbook running the code.

Also take another look at
Sheets("COPYHERE").Range("A" & lastrow + 1).Value = Date
Sheets("COPYHERE").Range("B" & lastrow + 1).Value = Time
Range("L43:X93").Copy Worksheets("COPYHERE").Range("A" & lastrow + 2)
Sheets, Worksheets and Range refer to ActiveWorkbook/ActiveSheet. Unless you're sure that's what you want, I recommend you change Sheets to ThisWorkbook.Sheets, Worksheet to ThisWorkbook.Worksheet and Range to ThisWorkbook.Sheets(<sheet name>).Range(<address>).

Thanks brother for your advice now its working fine either open many no of worksheet together — yatender singh, May 29 '20 at 06:39

score 0 · Answer 2 · answered May 28 '20 at 02:07

Sub Copy()
Application.ScreenUpdating = True

Dim lastrow As Integer
Dim wsName As String
Dim cpName As String

wsName = "28May2020"
cpName = "COPYHERE"
Sheets(wsName).Activate

lastrow = Sheets(cpName).Cells(Rows.Count, 2).End(xlUp).Row

With Sheets(cpName)
    .Range("A" & lastrow + 1).Value = Date
    .Range("B" & lastrow + 1).Value = Time
End With

Range("L43:X93").Copy Worksheets(cpName).Range("A" & lastrow + 2)

Sheets(cpName).Activate

Application.ScreenUpdating = False

End Sub

thanks for ur advice but now sheet is working fine – yatender singh May 29 '20 at 06:41 — yatender singh, May 29 '20 at 06:41

Select method of Worksheet Class Failed Error when opening another workbook

2 Answers2