ワークシート集約

「ディレクトリ名,ブック名,シート名,コピー先シート名」
のリストを2行目以降に書いて、下記のマクロを実行すると、
新しいブックに全部集約してくれる。

Option Explicit

Sub ワークシート集約()
    Dim bkInput As Workbook
    Set bkInput = ThisWorkbook
    
    Dim bkOutput As Workbook
    Set bkOutput = Workbooks.Add
    
    Dim shtInput As Worksheet
    Set shtInput = bkInput.Sheets(1)
    
    shtInput.Copy Before:=bkOutput.Sheets(1)

    Dim nRow As Integer
    nRow = 2
    Do While shtInput.Cells(nRow, 1) <> ""
        Dim sPath As String
        Dim sBook As String
        Dim sSheet As String
        Dim sNewSheet As String
        
        sPath = shtInput.Cells(nRow, 1)
        sBook = shtInput.Cells(nRow, 2)
        sSheet = shtInput.Cells(nRow, 3)
        sNewSheet = shtInput.Cells(nRow, 4)
        
        Dim bkData As Workbook
        Set bkData = Workbooks.Open(Filename:=sPath & "\" & sBook, ReadOnly:=True)
        
        bkData.Worksheets(sSheet).Copy After:=bkOutput.Worksheets(1)
        
        bkOutput.Worksheets(sSheet).Name = sNewSheet
        
        nRow = nRow + 1
    Loop
    
End Sub