Macro to Combine Workbooks

These VBA macros combine many Excel files from one folder: copying every sheet into the current workbook, or stacking all the data into a single sheet.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Copy every sheet from every file

Pick a folder; each sheet from each workbook in it is copied into this workbook:

Sub CombineWorkbooks()
    Dim p As String, f As String, wb As Workbook, ws As Worksheet
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = 0 Then Exit Sub
        p = .SelectedItems(1) & Application.PathSeparator
    End With
    Application.ScreenUpdating = False
    f = Dir(p & "*.xls*")
    Do While f <> ""
        If f <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(p & f, ReadOnly:=True)
            For Each ws In wb.Worksheets
                ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            Next ws
            wb.Close SaveChanges:=False
        End If
        f = Dir()
    Loop
    Application.ScreenUpdating = True
End Sub

If two sheets have the same name, Excel adds a number, such as Sales (2).

Stack all the data into one sheet

For monthly or branch files with the same columns, this copies every file’s first sheet below the last, keeping one header row:

Sub StackWorkbooks()
    Dim p As String, f As String, wb As Workbook, dest As Worksheet, r As Long, src As Range
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = 0 Then Exit Sub
        p = .SelectedItems(1) & Application.PathSeparator
    End With
    Set dest = ThisWorkbook.Worksheets.Add
    Application.ScreenUpdating = False
    f = Dir(p & "*.xls*")
    Do While f <> ""
        If f <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(p & f, ReadOnly:=True)
            Set src = wb.Sheets(1).UsedRange
            If r = 0 Then
                src.Copy dest.Range("A1")
            ElseIf src.Rows.Count > 1 Then
                src.Offset(1).Resize(src.Rows.Count - 1).Copy dest.Cells(r + 1, 1)
            End If
            r = dest.Cells(dest.Rows.Count, 1).End(xlUp).Row
            wb.Close SaveChanges:=False
        End If
        f = Dir()
    Loop
    Application.ScreenUpdating = True
End Sub

The first file’s header row is kept, and the headers of the others are skipped. The columns must be in the same order in every file.

Without a macro

Power Query is the best way to combine files you get every month: Data → Get Data → From File → From Folder → Combine & Transform. Add next month’s file to the folder and click Refresh. For a quick one-off, use the free merge Excel files tool, which matches columns by header name.

Things to know

  • Put only the files to combine in the folder.
  • Files are opened read-only, so they aren’t changed.
  • Password-protected files will ask for their password.

Related macros