VBA Combine Workbooks (Excel Files)

To combine multiple Excel workbooks, you need to use a code that can either let you choose the files that you want to combine or you can refer to a particular path location and combine all 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).

Dim Path As String
Dim Filename As String
Dim Sheet As Worksheet

Path = "C:UsersDellDesktopsample-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

Steps to Combine Excel Files using VBA

1. Open the visual basic editor (make sure to activate the developer tab if you have it on the ribbon already on the ribbon).
   
2. After that, locate the current workbook from the project window (control + r) and insert a new module there.
   
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).
   
4. From here, you need to change the value for the path variable with the folder location where you have all the Excel files that you want to combine.
   
5. In the end, run the code and you get all the worksheets from the workbook in the current file.

Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook

This code opens each file one by one and copies each worksheet from it one by one and pastes it to the current file with the same name. Following is the same code but with the screen updating false at the start to perform everything at the backend.

Dim Path As String
Dim Filename As String
Dim Sheet As Worksheet

Application.ScreenUpdating = False

Path = "C:UsersDellDesktopsample-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

Note: If you want to learn to combine data from multiple workbooks into a single table make sure to check out this guide.