Consolidate multiple sheets in multiple workbooks into one workbook with the same sheets but the data in the multiple sheets will be consolidated

Question

I tried looking for this across the web but for my purpose, I have been unable so far to optimize the code required. This is what I am trying to accomplish:

I have files called Excel 1, Excel 2, Excel 3 and Master Excel. All of the files have the same number of worksheets, worksheet name and the same structure when it comes to the header and such.

I am trying to consolidate the values of Excel 1, Excel 2 and Excel 3 to the Master file.

So on the Master File, if there is sheet named 1000, then copy paste a range from Excel 1 sheet named 1000. Then look for sheet 1000, in Excel 2 and copy paste a range on the blank line following the last row used on Master file Sheet 1000.

The range is always the row after the header (this is fixed on all sheets) till the last row with data on a specific column.

Now there are multiple sheets in each workbooks and all the worksheets will have the same name.

Also the filepath of the files will be constant so I dont want an option to choose from.

The below code is able to loop through the worksheets and I can also define the copy paste range perfectly but only issue with the below is that I dont know how to match a target sheet with a destination sheet meaning sheet 1000's data in excel 1 file to be pasted to sheet 1000 in the master file.

Sub test()

Dim MyFile As String, MyFiles As String, FilePath As String
Dim erow As Long
'~~> Put additional variable declaration
Dim wbMaster As Workbook, wbTemp As Workbook
Dim wsMaster As Worksheet, wsTemp As Worksheet

FilePath = "\\AM01PCIFS01.global.root\HomeDirs$\yameen.sarwar\Desktop\Test\II1\"
MyFiles = "\\AM01PCIFS01.global.root\HomeDirs$\yameen.sarwar\Desktop\Test\II1\*.xlsx"
MyFile = Dir(MyFiles)

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'~~> Set your declared variables
Set wbMaster = ThisWorkbook 'if you want to consolidate files in this workbook
Set wsMaster = wbMaster.Sheets("Sheet1") 'replace Sheet1 to suit

Do While Len(MyFile) > 0
    'Debug.Print MyFile
    If MyFile <> "master.xlsm" Then
        '~~> Open the file and at the same time, set your variable
        Set wbTemp = Workbooks.Open(Filename:=FilePath & MyFile, ReadOnly:=True)
        Set wsTemp = wbTemp.Sheets(1) 'I used index, you said there is only 1 sheet
        '~~> Now directly work on your object
        With wsMaster
            erow = .Range("A" & .Rows.Count).End(xlUp).Row 'get the last row
            '~~> Copy from the file you opened
            wsTemp.Range("A2:S20").Copy 'you said this is fixed as well
            '~~> Paste on your master sheet
            .Range("A" & erow).Offset(1, 0).PasteSpecial xlPasteValues
        End With
        '~~> Close the opened file
        wbTemp.Close False 'set to false, because we opened it as read-only
        Set wsTemp = Nothing
        Set wbTemp = Nothing
    End If
    '~~> Load the new file
    MyFile = Dir
Loop

With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

End Sub

Answer 1

Try this (see my comments in the code), but I made some small alterations in your Do While loop

Sub test()

Dim MyFile As String, MyFiles As String, FilePath As String
Dim erow As Long
'~~> Put additional variable declaration
Dim wbMaster As Workbook, wbTemp As Workbook
Dim wsMaster As Worksheet, wsTemp As Worksheet
Dim i As Integer

FilePath = "\\AM01PCIFS01.global.root\HomeDirs$\yameen.sarwar\Desktop\Test\II1\"
MyFiles = "\\AM01PCIFS01.global.root\HomeDirs$\yameen.sarwar\Desktop\Test\II1\*.xlsx"
MyFile = Dir(MyFiles)

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'~~> Set your declared variables
Set wbMaster = ThisWorkbook 'if you want to consolidate files in this workbook

Do While Len(MyFile) > 0
    'Debug.Print MyFile
    If MyFile <> "master.xlsm" Then
        '~~> Open the file and at the same time, set your variable
        Set wbTemp = Workbooks.Open(Filename:=FilePath & MyFile, ReadOnly:=True)
        'Start the loop of sheets within the source workbook
        For i = 1 To wbTemp.Sheets.Count
            Set wsTemp = wbTemp.Sheets(i) 'I used index, you said there is only 1 sheet
            '~~> Now directly work on your object
            With wbMaster.Worksheets(wsTemp.Name) 'This matches the sheet name in the source workbook to the sheet name in the target workbook
                erow = .Range("A" & .Rows.Count).End(xlUp).Row 'get the last row of target sheet
                '~~> Copy from the file you opened
                wsTemp.Range("A2:S20").Copy 'you said this is fixed as well
                '~~> Paste on your master sheet
                .Range("A" & erow).Offset(1, 0).PasteSpecial xlPasteValues
                Application.CutCopyMode = False
            End With
        Next i
        '~~> Close the opened file
        wbTemp.Close False 'set to false, because we opened it as read-only
    End If
    '~~> Load the new file
    MyFile = Dir
Loop

With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

End Sub

Answer 2

To take sheetnames in wbMaster and reference a sheet with the same name in wbTemp, you can pass the name through a variable. Here is a couple of lines that will loop through your sheets in wbMaster

Dim strSheetname as String

For i = 1 To wbMaster.Sheets.Count
      strSheetName = wbMaster.Sheets(i).Name
      Set wsTemp = wbTemp.Sheets(strSheetName)
      'Do whatever you need here with wsTemp
Next i

This code lacks error handling (i.e. if a sheet exists in wbMaster that does not exist in wbTemp, you will get an out of range error) but this will get you started.