Combine Worksheets from Multiple Workbooks into One

Add to Favorites

Excel macro that allows you to select multiple workbooks and have all of their worksheets automatically combined into a single workbook. 

This macro will add the worksheets to a newly created workbook and not to the currently open/visible workbook. This allows you to do whatever you want with the new workbook without having to worry about the contents of the current workbook.

This macro allows you to select as many files as you want to combine and you do it all through the regular "Select File" dialogue box, which makes it really easy to use.

You do not have to change anything for this macro to work, just run it.

Where to Install the Macro: Module

Sub Combine_Workbooks_Select_Files_1()

    Dim Fnum As Long
    Dim mybook As Workbook
    Dim BaseWb As Workbook
    Dim CalcMode As Long
    Dim FName As Variant
    Dim wSheet As Worksheet


    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With

    'Get the files to combine
    FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True)

    If IsArray(FName) Then

        'Create the workbook to store the combined files
        Set BaseWb = Workbooks.Add(xlWBATWorksheet)

        'Loop through the files to combine
        For Fnum = LBound(FName) To UBound(FName)
            Set mybook = Nothing
            On Error Resume Next
            Set mybook = Workbooks.Open(FName(Fnum))
            On Error GoTo 0
            If Not mybook Is Nothing Then
                On Error Resume Next

                'Add the worksheets to the new workbook
                For Each wSheet In mybook.Worksheets

                    wSheet.Move Before:=BaseWb.Sheets(1)

                Next wSheet

                mybook.Close savechanges:=False

                On Error GoTo 0

            End If

        Next Fnum

        'Delete a default worksheet that was added to the new workbook.
        BaseWb.Sheets(BaseWb.Sheets.Count).Delete

    End If

ExitTheSub:

    'Reset the options for display, alerts, and calculations.
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
        .DisplayAlerts = True
    End With

End Sub
 



How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Excel Forum