Consolidate all worksheets from multiple workbooks (.xlsx files) into single (current) workbook.

Office.Excel.ConsolidateWorkbooks.bas

' http://www.excel-easy.com/vba/examples/import-sheets.html
' https://trumpexcel.com/combine-multiple-workbooks-one-excel-workbooks/
' https://stackoverflow.com/questions/579797/open-excel-file-for-reading-with-vba-without-display
Sub ConsolidateWorkbooks()
    Application.ScreenUpdating = False
    Call AppendWorkbook("D:\workspace\cpp\InventoryRoutingProblem\Deploy\Doc\7 Analysis\", "2.16.CorrelationBetweenObjAndCost.xlsx", "2.16")
    Call AppendWorkbook("D:\workspace\cpp\InventoryRoutingProblem\Deploy\Doc\7 Analysis\", "2.18.CorrelationBetweenObjAndCost.xlsx", "2.18")
    Call AppendWorkbook("D:\workspace\cpp\InventoryRoutingProblem\Deploy\Doc\7 Analysis\", "2.20.CorrelationBetweenObjAndCost.xlsx", "2.20")
    Call AppendWorkbook("D:\workspace\cpp\InventoryRoutingProblem\Deploy\Doc\7 Analysis\", "2.26.CorrelationBetweenObjAndCost.xlsx", "2.26")
    Application.ScreenUpdating = True
    ThisWorkbook.Worksheets(1).Activate
End Sub
Sub AppendWorkbook(fileDir As String, filename As String, sheetNamePrefix As String)
    Dim sheet As Worksheet
    
    Workbooks.Open Filename:=fileDir & filename, ReadOnly:=True
    ActiveWindow.Visible = False
    For Each sheet In Workbooks(filename).Sheets
        sheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        ActiveSheet.Name = sheetNamePrefix '& sheet.Name
    Next sheet
    Workbooks(filename).Close False
End Sub

When running this, I received this puzzling error:

Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook, you can select the data, and then use the Copy and Paste commands to insert it into the sheets of another

(When using Copy/Paste I get a different error related to the size. I have about 100k rows in the sheet).

@canadaduane Sorry, I have no idea about your error.
I'm not an expert on Excel or VBA, this snippet is obtained by macro recorder.
Maybe it's just because your files are too big.

I suspect it has something to do with the way Excel creates ranges in its worksheets. I don't know much about it, but it seems the app pre-allocates cells as a range, and I'm wondering if I need to somehow tell it to pre-allocate a larger range when importing larger files.

In any case, thanks for your thoughtful reply!