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
 




Similar Content on TeachExcel
Combine Multiple Workbooks into One
Macro: This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets...
Combine Values from Multiple Cells into One Cell in Excel
Tutorial: There are two easy ways to combine values from multiple cells in Excel. In order to do thi...
Guide to Combine and Consolidate Data in Excel
Tutorial: Guide to combining and consolidating data in Excel. This includes consolidating data from ...
Combine Data from Multiple Worksheets in Excel
Tutorial: The easiest way to combine and consolidate data in Excel. Simple method to combine data ...
Import a Worksheet from One Workbook to Another in Excel
Tutorial: In Excel, you can quickly copy an entire worksheet from one workbook to another workbook. ...
Quickly Combine a List of Values and Put a Delimiter Between Each Value in Excel
Tutorial: How to combine a list of data into one cell while putting a delimiter between each piece ...



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.


Similar Content
Combine Multiple Workbooks into One
Macro: This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets...
Combine Values from Multiple Cells into One Cell in Excel
Tutorial: There are two easy ways to combine values from multiple cells in Excel. In order to do thi...
Guide to Combine and Consolidate Data in Excel
Tutorial: Guide to combining and consolidating data in Excel. This includes consolidating data from ...
Excel Forum