excel - 激活工作簿/工作表并复制到不同工作簿中的下一个空行

Question

我有一个包含打开 Workbook 2 的宏的主文件。

在工作簿 2 中，有一个要从中复制的工作表，但在打开文件时它并不总是当前工作表。

我需要激活此工作表，将某个范围复制到我的主文件中的下一个空行。

我的代码似乎复制了我的主文件而不是 Workbook 2 中的现有数据。

这是我的代码：

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

标签： excelvba

Answer 1

我已经为您评论了您的代码，希望对您有所帮助。

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