Excel Combine Workbooks as Separate Sheets

This video shows how to combine multiple workbooks as separate worksheets in the resulting combined workbook. In other words, it does not merge all of the data from all of the sheets into a single table. It simply gathers all of the individual worksheets into a single combined workbook.
- Base code (VBA macro)
- Run button
- File path

Chapters in this video:
00:00 - Introduction
00:20 - Base Macro
01:23 - Run Button
01:52 - File Path

VBA CODE:

Sub CopyAllWorksheets_Browse()

Dim ws As Worksheet
Dim wbSource As Workbook, wbDest As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String

' Retrieve Target Folder Path From User
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select a Folder"
.AllowMultiSelect = False
.Show
myPath = .SelectedItems(1) & "\"
End With

' Target File Extension (must include wildcard "*")
myExtension = "*.xls*"

' Create a new workbook
Set wbDest = Workbooks.Add

' Target Path with Ending Extention
myFile = Dir(myPath & myExtension)

' Loop through each Excel file in folder
Do
' Set variable equal to opened workbook
Set wbSource = Workbooks.Open(Filename:=myPath & myFile)

' Loop through each worksheet in Workbook
For Each ws In wbSource.Worksheets
ws.Copy After:=wbDest.Sheets(wbDest.Sheets.Count)
Next ws

' Close the source workbook without saving
wbSource.Close SaveChanges:=False

' Get the next file name
myFile = Dir
Loop Until myFile = ""

' Remove the initial blank worksheet from the new workbook without warning
Application.DisplayAlerts = False
wbDest.Sheets(1).Delete
Application.DisplayAlerts = True

MsgBox "Operation Complete"

End Sub

Also, check out these videos with time saving Excel Hacks:

[camen]

You are awesome! Thank you for sharing!

[markbaxter]

Hi Jeff, really useful code but when I try it it fails. I have a folder with 2 files in it. When I run the code it opens a new workbook and copies in the sheet from the first file in the folder. It then opens the second file but then gives and error message saying Method 'Copy' of object '_Worksheet' failed. When I debug it highlights the following in the code:

' Loop through each worksheet in Workbook
For Each ws In wbSource.Worksheets
ws.Copy
Next ws


Any ideas?

[AlekaPapageorgiou]

Thank you so much for the insightful video! Is there a way to define the xls import file order?

[MVls-su]

Thank you so much!!!! This is exactly what I needed. I have a question. I have to do this hundreds of times. Do I have to do step 1 each time or will step 2 save me from that. Example I have 300 regions, each region has multiple workbooks that I have to combine into one master workbook so that I can send it to that regional manager. So in the end I should have 300 master workbooks, each workbook would have the combined workbooks for that region. Would I have to do the VBA macro each time or could I use step two to run the macro and just select the next batch of files. I appreciate your