Skip to content

Excel VBA: Combine Multiple Workbooks into One

    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