Excel VBA consolidate multiple workbooks into one workbook

This Excel VBA tutorial explains how to consolidate multiple workbooks into one workbook.

You may also want to read:

Excel VBA Consolidate worksheets into one worksheet

Excel VBA combine worksheets columns into one worksheet

Consolidate multiple workbooks into one workbook

Assume that you have a lot of workbooks, each workbook contains multiple worksheets. You may want to copy all worksheets of each workbook into a master workbook.

For example, I have a folder called “sample” under C:\Users\WYMAN\Desktop\sample

which contains three workbooks I want to consolidate

consolidate multiple workbooks into one workbook 01

Workbook a contains one worksheet wbA ws1

Workbook b contains two worksheets: wbB ws1 and wbB ws2

Workbook c contains three worksheets: wbC ws1, wbC ws2, wbC ws3

Consolidate multiple workbooks – Procedure

Create a workbook called consolidate workbooks.xlsm, which only contains a blank worksheet Sheet1

Press ALT+F11 to enter into VBE, then insert a new Module, copy and paste the below code

Public Sub consolWB()
    Dim FSO As Object
    Dim folder As Object, subfolder As Object
    Dim wb As Object
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    folderPath = <span style="color: #0000ff;">"C:\Users\WYMAN\Desktop\sample"</span>
    Set folder = FSO.GetFolder(folderPath)
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
    End With
        
    For Each wb In folder.Files
        If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then
            Set openWB = Workbooks.Open(wb)
            For Each ws In openWB.Worksheets
                ws.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            Next ws
            openWB.Close
        End If
    Next wb
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .AskToUpdateLinks = True
    End With
End Sub

Consolidate multiple workbooks – Result

Press F5 to run the Macro you just pasted, now you have consolidated all the worksheets of all workbooks under folder C:\Users\WYMAN\Desktop\sample

consolidate multiple workbooks into one workbook 02

After combining all worksheets, you can sort the worksheets in alphabetic order. Click here for details.

Sub sortWS()
    For i = 1 To Worksheets.Count
        For j = i To Worksheets.Count
            If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then
                  Sheets(j).Move before:=Sheets(i)
             End If
        Next j
    Next i
End Sub

In case you don’t want to write VBA, you can click here to download my simple template.

consolidate multiple workbooks into one workbook 03

 

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *