VBA Combinng Excel files from multiple folders into one worksheet based on having the same file name

VBA: Combine Excel files from multiple folders into one worksheet based on having the same 4 first characters in the file name.xlsx


Description:

There are files with a customer ID from 001-100. Each customer has 3 different Excel reports (### Report1-3), which are generated into different folders (Folders1-3).

For example: Folder 1 will have:
001 Report1.xlsx

100 Report1.xlsx

Folder 2 will have:
001 Report2.xlsx

100 Report2.xlsx

Folder 3 will have:
001 Report3.xlsx

100 Report3.xlsx

Each of these files contains only one sheet with Values (no formulas nor PivotTables). Each Report 1, Report 2, Report 3 have a different set of columns/rows.


Question:

I would like to create one workbook for each Customer ID “### ” and have the reports as Sheets (### Report1) (### Report 2) (### Report 3) (All the desired documents to be combined into one workbook will have the first four characters at the beginning of each report’s name)

For example for Customer: 001
Create one “001 AllReports.xlsx” workbook that has:
Sheet 1 = 001 Report1
Sheet 2 = 001 Report2
Sheet 3 = 001 Report3

Then move to Customer ID 002 and do the same.

Please let me know if anything else needs to be clarified. Thanks, I truly appreciate it!


Some notes/thoughts:

I was thinking of creating a Merger Macro template that has 3 fields. Where I fill in Folder 1, then Folder 2, and Folder 3 paths and then proceed to merge the documents based on the Customer ID.

I have only merged basic file before based of:
https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.copy

I was also curious about doing it with a query but since the columns are not consistent across Report1-3 I am not sure if it would be even an option.