30 Advanced Excel Tips for 2026

Excel Champs / VBA / VBA Combine Workbooks (Excel Files)
By Puneet Gogia (Microsoft MVP)

VBA Combine Workbooks (Excel Files)

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

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

  1. Open the Visual Basic Editor (make sure to activate the Developer tab if you don’t have it on the ribbon already).
    open the visual basic editor
  2. After that, locate the current workbook in the Project window (Ctrl + R) and insert a new module there.
    locate the current workbook
  3. 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).
    code window in the module
  4. 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).
    change the value for the path variable
  5. 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.

Puneet Gogia

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.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel