Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
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
Â
Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Similar Content on TeachExcel
Combine Multiple Workbooks into One
Macro: This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets int...
Macro: This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets int...
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...
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 ...
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 ...
Tutorial: The easiest way to combine and consolidate data in Excel. Simple method to combine data ...
Vlookup Across Multiple Workbooks
Tutorial: How to use the VLOOKUP function across multiple workbooks in Excel. This will create a lin...
Tutorial: How to use the VLOOKUP function across multiple workbooks in Excel. This will create a lin...
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.Â...
Tutorial: In Excel, you can quickly copy an entire worksheet from one workbook to another workbook.Â...
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
- 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.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 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.
- You are now ready to run the macro.