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
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.