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

 

Posted by Chester Tugwell