This video demonstrates how to combine all Excel workbooks in a folder into a new workbook using VBA. All sheets in each workbook will be copied to a new workbook.
The VBA code featured in the video…
Sub CombineWorkbooks() Dim Path As String Path = "C:\Users\Blue Pecan\Desktop\Combine Workbooks Example\" Dim FileName As String FileName = Dir(Path & "*.xlsx") Dim ws As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False Do While FileName <> "" Workbooks.Open Path & FileName For Each ws In ActiveWorkbook.Sheets ws.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) Next ws Workbooks(FileName).Close FileName = Dir() Loop Worksheets(1).Delete Application.ScreenUpdating = False Application.DisplayAlerts = False End Sub