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


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.


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.

To merge basic files before I was 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.

This example/VBA is good if all the files are on the same folder: It would be great to add the different folder paths and based it off the names containing the customer ID ###.

Sub ConslidateWorkbooks()
'Created by Sumit Bansal from https://trumpexcel.com
Dim FolderPath As String
Dim Filename As String
Dim Sheet As Worksheet
Application.ScreenUpdating = False
FolderPath = Environ("userprofile") & "\Desktop\Test\"
Filename = Dir(FolderPath & "*.xls*")
Do While Filename <> ""
 Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True
 For Each Sheet In ActiveWorkbook.Sheets
 Sheet.Copy After:=ThisWorkbook.Sheets(1)
 Next Sheet
 Filename = Dir()
Application.ScreenUpdating = True
End Sub
  • There are several question on looping through subfolders that are a good place to start – cybernetic.nomad Feb 6 at 21:51
  • Thanks! Any idea on how to filter by name first in order to merge the files? (to only combine the ones that start with the same name) Then it will go to loop through the subfolder. (From what I was thinking) – Pepe Feb 6 at 21:55
  • Loop through the files and check the filenames before opening them – cybernetic.nomad Feb 6 at 21:57
  • Would that still be possible if the paths are constantly changing? (sorry new to this... I have worked with macros mainly within the same document before) – Pepe Feb 6 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.