VBA & PQ - Merge All Excel Sheets

Introduction

Managing multiple Excel sheets can be tedious, especially when you need to consolidate data into a single worksheet. This VBA macro automates the merging process, allowing you to combine entire sheets or selectively merge based on specific column headings.

Methods to Consolidate Excel Sheets

There are two methods to consolidate the excel sheets,

1. Using Power Query (Minimal Manual Work)

• A modern and powerful tool in Excel for data transformation and consolidation. Best suited for one-time tasks or users who prefer a GUI-based solution.

2. Using VBA Code (Manual Setup Required)

• Ideal for users who want more control and automation. Best suited for recurring tasks with consistent sheet structures.

Watch the Tutorial to consolidate the Data using Power Query

In this video, I demonstrate how to consolidate multiple Excel sheets using Power Query — a faster and more dynamic approach that requires minimal manual input.

VBA Code related to Power Query

Sub Load_Power_Query()
'=====================================
'Created by Harsh C
'=====================================
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim qryName As String
    Dim formula As String
    Dim counter As Integer

    ' Set reference to the current workbook
    Set wb = ActiveWorkbook

    ' Counter for naming queries if table name is not suitable
    counter = 1

    ' Loop through each worksheet in the workbook
    For Each ws In wb.Worksheets
        ' Check if worksheet contains tables
        If ws.ListObjects.Count > 0 Then
            ' Loop through each table in the worksheet
            For Each tbl In ws.ListObjects
                ' Create a unique name for the query based on the table name
                qryName = "Query_" & Replace(tbl.Name, " ", "_")

                ' Create the M formula with proper syntax
                formula = "let" & vbNewLine & _
                          "    Source = Excel.CurrentWorkbook()," & vbNewLine & _
                          "    #""" & tbl.Name & """ = Source{[Name=""" & tbl.Name & """]}[Content]" & vbNewLine & _
                          "in" & vbNewLine & _
                          "    #""" & tbl.Name & """"

                ' Try to add the query, if it already exists, append a counter to the name
                On Error Resume Next
                wb.Queries.Add qryName, formula

                If Err.Number <> 0 Then
                    Err.Clear
                    qryName = "Query_" & Replace(tbl.Name, " ", "_") & "_" & counter
                    counter = counter + 1
                    wb.Queries.Add qryName, formula
                End If
                On Error GoTo 0

                Debug.Print "Added query: " & qryName & " for table " & tbl.Name & " in " & ws.Name
            Next tbl
        End If
    Next ws

    MsgBox "All tables have been loaded into Power Query. Open the Power Query Editor to view them.", vbInformation
End Sub

Using the above given VBA Code you can easily load all the tables to the Power Query. Once the tables are loaded to Power Query just follow the steps as shown in the video.

Use Case of VBA Code without using Power Query

This macro is useful for:

• Consolidating data from multiple sheets into one.
• Automating repetitive data compilation tasks.
• Saving time on manual copy-paste.

How It Works

The macro provides two merging options:

1. Merge all sheets - Combines data from every sheet into a new “CombinedData” sheet.
2. Selective headings merge - Copies only user-selected column headings into a “Selective Headings” sheet.

The macro prompts the user for:

• Whether to merge all sheets or select specific headings.
• The column letter to identify the last row of data.
• The range of headings to be merged (if selective merging is chosen).

VBA Code

Sub Merge_Combine()
'=====================================
'Created by Harsh C
'=====================================

    Dim ws As Worksheet
    Dim combinedSheet As Worksheet
    Dim lastRow As Long
    Dim combinedRow As Long
    Dim colName As String
    Dim colNumber As Long
    Dim wsRange As Range
    Dim userResponse As VbMsgBoxResult
    Dim headingRange As Range
    Dim finalDumpSheet As Worksheet
    Dim colHeader As Range

    ' Reference active workbook
    Dim wb As Workbook
    Set wb = ActiveWorkbook

    ' Ask user for merging option
    userResponse = MsgBox("Do you want to merge all sheets?", vbYesNo + vbQuestion, "Merge Option")

    If userResponse = vbYes Then
        Set combinedSheet = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
        combinedSheet.Name = "CombinedData"
        combinedSheet.Cells(1, 1).Value = "Sheet Name"
        combinedRow = 2
        colName = InputBox("Enter column letter to check last row:", "Column Name Input")

        On Error Resume Next
        colNumber = Range(colName & "1").Column
        On Error GoTo 0

        If colNumber <= 0 Then
            MsgBox "Invalid column letter.", vbExclamation
            Exit Sub
        End If

        For Each ws In wb.Worksheets
            If ws.Name <> combinedSheet.Name Then
                combinedSheet.Cells(combinedRow, 1).Value = ws.Name
                lastRow = ws.Cells(ws.Rows.Count, colNumber).End(xlUp).Row

                If lastRow > 0 Then
                    Set wsRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, ws.UsedRange.Columns.Count))
                    wsRange.Copy Destination:=combinedSheet.Range("B" & combinedRow)
                    combinedRow = combinedRow + lastRow
                End If
                combinedRow = combinedRow + 1
            End If
        Next ws

        MsgBox "All sheets merged successfully!", vbInformation
    Else
        Set headingRange = Application.InputBox("Select heading range:", Type:=8)
        If headingRange Is Nothing Then Exit Sub

        Set finalDumpSheet = wb.Sheets("Selective Headings")
        If finalDumpSheet Is Nothing Then
            MsgBox "Sheet 'Selective Headings' not found.", vbExclamation
            Exit Sub
        End If

        combinedRow = 2
        For Each ws In wb.Worksheets
            If ws.Name <> "Final Dump" Then
                lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

                For Each colHeader In headingRange
                    colNumber = Application.Match(colHeader.Value, ws.Rows(1), 0)
                    If Not IsError(colNumber) Then
                        Set wsRange = ws.Range(ws.Cells(2, colNumber), ws.Cells(lastRow, colNumber))
                        wsRange.Copy Destination:=finalDumpSheet.Cells(combinedRow, colHeader.Column)
                    End If
                Next colHeader
                combinedRow = combinedRow + lastRow
            End If
        Next ws

        MsgBox "Selected content merged into 'Final Dump' successfully!", vbInformation
    End If
End Sub

Not Convinced with VBA Code and Power Query?

• With the latest Excel updates, the VSTACK function makes it incredibly easy to combine data from multiple sheets vertically. And the best part? You can automatically remove blank cells from your result using a simple dot ”(.)” in the formula.

For Instance

Let’s say you want to combine line items listed in column A (from rows A4 to A75) across three different sheets named Data_1, Data_2, and Data_3.

=VSTACK(Data_1!A.:.A,Data_2!A.:.A,Data_3!A.:.A)

In this formula, the use of . (dot) between each column reference tells Excel to trim out empty cells automatically, giving you a cleaner, gap-free output.

How to Use

• Open your Excel workbook containing multiple sheets.
• Press ALT + F11 to open the VBA editor.
• Insert a new module and paste the code above.
• Run the M17_Merge_Combine macro.
• Follow the prompts to merge data according to your preference.

Conclusion

This VBA macro simplifies data consolidation in Excel by providing flexible merging options. Whether you’re working with financial reports, survey data, or other multi-sheet datasets, this automation will save you time and effort. Give it a try and streamline your workflow!