Combine Worksheets from Multiple Workbooks into One

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

 

---