File Download: https://www.exceluniversity.com/yt_r...

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


Unlock hidden Excel tricks and hacks, and become a timesaving Excel pro exceluniversity.com/challenge⚡ Say goodbye to late nights and spreadsheet struggles!

Subscribe here https://www.youtube.com/c/ExcelUniver... and turn on all notifications for MS Excel time hacks that will make you more productive!

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

Excel PivotTables for Beginners (2023)    • Excel PivotTables for Beginners (2024)  

Watch VLOOKUP Hacks Playlist here    • VLOOKUP Hacks Series  

Watch VLOOKUP vs SUMIFS Playlist here    • VLOOKUP vs SUMIFS Series  

Watch Dynamic Arrays playlist here    • Dynamic Arrays Series  

Watch Treasure Maps playlist here    • Treasure Maps Series  

Watch Slow to Fast playlist here    • Slow to Fast Series