將多個工作簿合併為一個工作簿（已關閉）

Question

我需要將多個工作簿合併為一本工作簿。

來源工作簿具有唯一的工作表名稱 =“job”

目標工作簿有多個工作表名稱

下面的程式碼有兩個問題，

1. For 迴圈不起作用
2. 目標工作簿中貼上的資料會建立一個新工作表。但我需要將資料貼到現有工作表中。

---

Sub combine()

'destination worksheets    
    Dim Ar As Worksheet
    Dim nr As Worksheet
    Set Ar = ThisWorkbook.Sheets("sheetAr")
    Set nr = ThisWorkbook.Sheets("Sheetnr")
    'Source workbooks
    Dim FolderPath As String
    Dim Filename As String
    Application.ScreenUpdating = False
    FolderPath = Environ("userprofile" & "\Desktop\Copy")
    Filename = Dir(FolderPath & "*.xlsx*")
    
    Do While Filename <> ""
    Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True
    Dim ws As Worksheet
    Dim AW As Workbook
    
    Set AW = ActiveWorkbook
    Set ws= ActiveWorkbook.Sheets("Job")
    
     For Each AW In ws
     AW.Activate
     Cells.ShownAll
     ws.Copy Ar
     Next AW
     Workbooks(Filename).Close savechanges = True
     Filename = Dir()
    Loop
    Application.ScreenUpdating = True
    End Sub`