Loop through the folder with Dir, open each file and copy its worksheets into the workbook that holds the macro:
Sub CombineWorkbooks()
Dim folderPath As String, fileName As String
Dim wb As Workbook, ws As Worksheet
folderPath = "C:\Reports\Monthly\"
Application.ScreenUpdating = False
fileName = Dir(folderPath & "*.xls*")
Do While fileName <> ""
If fileName <> ThisWorkbook.Name Then
Set wb = Workbooks.Open(folderPath & fileName, ReadOnly:=True)
For Each ws In wb.Worksheets
ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next ws
wb.Close SaveChanges:=False
End If
fileName = Dir()
Loop
Application.ScreenUpdating = True
End Sub
The folder path must end with a backslash. *.xls* picks up .xlsx, .xlsm and .xls files; change it to *.xlsx to take only those. Copying after the last sheet keeps the files in the order they are read, while copying after Sheets(1) reverses it.
Sheets with the same name do not overwrite each other. Excel adds a suffix, so the second “Data” sheet becomes “Data (2)”. Rename the sheets after copying if you need to know which file each came from, keeping names to 31 characters or fewer.
To stack the rows from every file into one table instead of separate sheets, use Power Query: Data → Get Data → From File → From Folder, then Combine → Combine & Transform Data. It needs no code, works in Excel 2016 and later and Microsoft 365, and picks up new files when you click Refresh.
Keep the ThisWorkbook.Name check if the macro file sits in the same folder. Without it, the loop opens the macro file itself, and the Close line then shuts it and ends the macro partway through.
To combine multiple Excel workbooks, you need to use a code that either lets you choose the files that you want to combine or refers to a particular path location and combines all the Excel files there in the current workbook.
VBA Combine Multiple Workbooks
You can use the following code which uses the location that I have mentioned in the path variable (a folder from my system’s desktop).
Sub vba_combine_workbooks() Dim Path As String Dim Filename As String Dim Sheet As Worksheet Path = "C:\Users\Dell\Desktop\sample-data\" Filename = Dir(Path & "*.xlsx") Do While Filename <> "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Next Sheet Workbooks(Filename).Close Filename = Dir() Loop End Sub
Steps to Combine Excel Files using VBA
- Open the Visual Basic Editor (make sure to activate the Developer tab if you don’t have it on the ribbon already).

- After that, locate the current workbook in the Project window (Ctrl + R) and insert a new module there.

- You’ll have a code window in the module (double click on it) where you need to paste the code that you have (as it is).

- From here, you need to change the value of the Path variable to the folder location where you have all the Excel files that you want to combine (keep the backslash at the end).

- In the end, run the code, and you get all the worksheets from the workbooks in the current file.
This code opens each file one by one, copies each worksheet from it, and pastes it into the current file with the same name (if a sheet with that name already exists, Excel adds a number, like “Sheet1 (2)”). Following is the same code but with screen updating turned off at the start to perform everything in the background.
Sub vba_combine_workbooks_fast() Dim Path As String Dim Filename As String Dim Sheet As Worksheet Application.ScreenUpdating = False Path = "C:\Users\Dell\Desktop\sample-data\" Filename = Dir(Path & "*.xlsx") Do While Filename <> "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Next Sheet Workbooks(Filename).Close Filename = Dir() Loop Application.ScreenUpdating = True End Sub
Both codes only pick up .xlsx files. To include .xls and .xlsm files as well, change “*.xlsx” to “*.xls*”. And don’t keep the workbook with this code in the same folder, as the code would try to open it again.
Choose the Files to Combine
If you don’t want to combine all the files in a folder, you can let the user pick the files. The GetOpenFilename method opens the Open dialog box, and with MultiSelect:=True, you can select more than one file:
Sub combine_selected_workbooks()
Dim files As Variant
Dim i As Long
Dim wb As Workbook
Dim Sheet As Worksheet
files = Application.GetOpenFilename( _
FileFilter:="Excel Files (*.xls*), *.xls*", MultiSelect:=True)
If Not IsArray(files) Then Exit Sub ' Cancel was clicked
Application.ScreenUpdating = False
For i = LBound(files) To UBound(files)
Set wb = Workbooks.Open(Filename:=files(i), ReadOnly:=True)
For Each Sheet In wb.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next Sheet
wb.Close SaveChanges:=False
Next i
Application.ScreenUpdating = True
End Sub
This code also adds the sheets at the end of the current workbook, so they stay in the same order as in the source files.
Frequently Asked Questions
Why does the code not combine any files?
Most of the time, the path is wrong or doesn’t end with a backslash, so Dir doesn’t find any file. Check the folder path and make sure it ends with \.
How do I combine only the first sheet of each workbook?
Replace the For Each loop with one line that copies only the first sheet: ActiveWorkbook.Sheets(1).Copy After:=ThisWorkbook.Sheets(1).
Can I combine the data into one sheet instead?
Yes, but these codes copy whole sheets. To stack the data into a single table, Power Query is the easier way; see the guide linked above.
Note: If you want to learn to combine data from multiple workbooks into a single table make sure to check out this guide.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.