Activate Workbook/sheet and copy to next empty row in a different workbook

Question

I have a master file which contains my macro to open Workbook 2.

Within Workbook 2, there is a sheet to copy from but it is not always the current sheet when the file is opened.

I need to activate this sheet, copy a certain range to the next empty row in my master file.

My code seems to copy the existing data in my master file not Workbook 2.

Here is my code:

Sub Get_TLS()

Application.ScreenUpdating = False

Filename = ActiveWorkbook.Name

Workbooks.OpenText Filename:= _
"THIS IS MY FILE PATH\*.xlsx"

Workbooks("Workbook2").Activate
Worksheets("SHEET TO COPY FROM").Activate

lastrow = ActiveSheet.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

Range("A3:GC" & lastrow).Copy

ThisWorkbook.Activate

Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste

End Sub

See the accepted answer to [how to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). You don't need to `Activate`, instead you need to work with workbook and worksheet variables. — BigBen, Jan 03 '19 at 05:01

score 1 · Accepted Answer · answered Jan 03 '19 at 05:59

I have commented your code for you and hope it helps.

Sub Get_TLS()

    ' Declare your variables!
    Dim Wb2 As Workbook
    Dim Ws As Worksheet, Ws2 As Worksheet
    Dim LastRow As Long

    Application.ScreenUpdating = False


    ' Note: At this point, very likely, the ActiveWorkbook is ThisWorkbook
    ' ThisWorkbook is the Workbook containing this code.
    ' ActiveWorkbook is the workbook you last looked at.
'    Filename = ActiveWorkbook.Name     Why would you want this name?

    ' Filename is the name of a property of the OpenText method
    ' it's different from your string variable by the same name.
    ' Rule #1: If VBA wants to use a name, you can't use it!
        ' OpenText opens a text file.
        ' If your file is of xlsx type then it isn't of txt type
        ' Therefore use the Open method, not OpenText.
            ' The asteric in *.xlsx is placeholder for any text.
            ' The property Filename can't process "any text".
            ' It needs precise instructions. Replace with precise file name.
    Workbooks.OpenText Filename:="THIS IS MY FILE PATH\*.xlsx"

    ' The newly opened workbook is now the "ActiveWorkbook"
    ' If its name is "Workbook2" then use ...\Workbook2.xlsx" as Filename above.
'    Workbooks("Workbook2").Activate
    ' you don't need to activate this Wb because it is already the ActiveWorkbook.
    ' Moreover, you don't want it to be active. You want ThisWorkbook
    ' to be active and remain active at the end.
    ' But you want to be able to refer to Workbook2. Therefore:-
    Set Wb2 = ActiveWorkbook
        ' instead of this code you might have used this line above:-
        ' Set Wb2 = Workbooks.OpenText(Filename:="THIS IS MY FILE PATH\*.xlsx")

    ' you don't want to activate this sheet.
'    Worksheets("SHEET TO COPY FROM").Activate
    ' But you want to refer to it. Therefore:-
    Set Ws2 = Wb2.Worksheets("Sheet to copy from")


    ' I didn't check this code, hope it works.
    LastRow = Ws2.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Ws2.Range("A3:GC" & LastRow).Copy

    ' Which worksheet you wish to paste to?
    ' Don't rely on "ActiveSheet" !!!  Therefore:-
    Set Ws = ThisWorkbook.Worksheets("MasterSheet")
    Ws.Range("A" & Ws.Rows.Count).End(xlUp).Offset(1).PasteSpecial
    '   consider this syntax instead:-
    '   Ws.Cells(Ws.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial


    ' This is necessary because the Open command made Wb2 active
    ThisWorkbook.Activate

    Application.ScreenUpdating = True
End Sub

Activate Workbook/sheet and copy to next empty row in a different workbook

1 Answers1