如何打开工作簿、复制数据并添加到另一个工作簿的最后一行?

Question

是否可以从当前工作簿打开另一个工作簿，并将表列上的数据复制并粘贴到当前工作簿中表的最后一行？并删除源数据。

Answer 1

改编第 5 个变量并尝试以下代码：

Option Explicit
Sub test()
Application.DisplayAlerts = False
Dim wb As Workbook
Set wb = ThisWorkbook
'*******************************************
'Adapt this vars
Dim path_wbToOpen As String
Dim myRange As String
Dim sheet_opened As String
Dim ws_final As Worksheet
Dim lastRow As Integer
path_wbToOpen = "C:testtest.xlsx" 'path of workbook where is the data
myRange = "A1:D1" 'Range to cpy
sheet_opened = "sheet_opened" 'name in opened WB where is sheet data
Set ws_final = wb.Sheets("sh_test") 'Sheet in current WB to paste data
lastRow = ws_final.Range("A" & Rows.Count).End(xlUp).Row + 1 ' set the last row (adapt column to check last row)

'*******************************************
'open another workbook from current one
Dim wbToOpen As Workbook
Set wbToOpen = Workbooks.Open(path_wbToOpen)
'Copy Data from opened WB
wbToOpen.Sheets(sheet_opened).Range(myRange).Copy 'Copy the data
'current WB
ws_final.Range("a" & lastRow).PasteSpecial ' past to the last row
'Clear Data and close WB
wbToOpen.Sheets(sheet_opened).Range(myRange).Clear 'Clear Data
wbToOpen.Close 'close WB
Application.DisplayAlerts = True
End Sub