VBA Code - Merge All Excel Sheets

UnmergeAndConsolidateSheets VBA Code: Functionality and Instant Solutions

Excel is a powerhouse for data management, but it’s not without its challenges—especially when dealing with merged cells and scattered data across multiple worksheets. Enter the UnmergeAndConsolidateSheets VBA script, a robust solution crafted to streamline these issues with automation. Built using Visual Basic for Applications (VBA), this code is a must-have for anyone looking to save time and reduce errors in Excel. In this article, we’ll explore what this code does, the instant troubles it addresses, and why it’s a vital tool for Excel users.

What Does the UnmergeAndConsolidateSheets Code Do?

The UnmergeAndConsolidateSheets subroutine is designed to simplify Excel workbook management by performing two key tasks: unmerging cells and consolidating data into a single sheet. Here’s how it works step-by-step:

Checks for Existing Sheet: It first checks if a "Consolidated Data" sheet exists. If found, it deletes it to avoid overlap, suppressing the confirmation prompt for a seamless process.
Creates a New Sheet: A fresh worksheet named "Consolidated Data" is added to the workbook, serving as the destination for all consolidated data.
Unmerges Cells: The script loops through every worksheet (except the new one), identifies merged cells, and unmerges them, ensuring data uniformity.
Consolidates Data: Starting from row 2 of each sheet, it copies all data rows and stacks them vertically in the "Consolidated Data" sheet, tracking the destination row dynamically.
Improves Presentation: Finally, it auto-fits the columns in the consolidated sheet for readability and cleans up by releasing memory resources.

Below is the complete VBA code:

Sub UnmergeAndConsolidateSheets()
    Dim ws As Worksheet
    Dim newWs As Worksheet
    Dim lastRow As Long
    Dim destRow As Long
    
    ' Check if a sheet with the name "Consolidated Data" already exists
    On Error Resume Next
    Set newWs = ThisWorkbook.Sheets("Consolidated Data")
    On Error GoTo 0
    
    ' Delete the existing "Consolidated Data" sheet if it exists
    If Not newWs Is Nothing Then
        Application.DisplayAlerts = False ' Turn off alerts to avoid confirmation prompt
        newWs.Delete
        Application.DisplayAlerts = True ' Turn alerts back on
    End If
    
    ' Create a new worksheet to consolidate data
    Set newWs = ThisWorkbook.Sheets.Add
    newWs.Name = "Consolidated Data"
    destRow = 1
    
    ' Loop through all sheets in the workbook
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> newWs.Name Then ' Skip the consolidated sheet
            ' Unmerge cells in the current sheet
            ws.Cells.UnMerge
            
            ' Loop through each row in the current sheet
            lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            If lastRow > 1 Then ' Check if there is data in the sheet
                ' Copy data to the consolidated sheet
                ws.Rows("2:" & lastRow).Copy newWs.Cells(destRow, 1)
                destRow = destRow + (lastRow - 1)
            End If
        End If
    Next ws
    
    ' Autofit columns in the consolidated sheet for better visibility
    newWs.Cells.EntireColumn.AutoFit
    
    ' Clean up
    Set newWs = Nothing
End Sub

The Instant Troubles This Code Addresses

This VBA script directly tackles two pervasive Excel problems that frustrate users:

Merged Cells Nightmares: Merged cells, while visually appealing, are a nightmare for data manipulation. They disrupt sorting, filtering, and copying tasks, often leading to errors or incomplete results. By unmerging all cells across sheets, this script eliminates these issues, making data ready for analysis or reporting.
Fragmented Data Across Sheets: When data is spread across multiple worksheets, manually consolidating it is tedious and prone to mistakes—like missing rows or duplicating entries. This code automates the process, instantly pulling all relevant data into one sheet, saving time and ensuring accuracy.

Why This VBA Script is a Game-Changer

For professionals like accountants, data analysts, or project managers, the UnmergeAndConsolidateSheets script is a productivity booster. It transforms chaotic workbooks into organized, analysis-ready files in seconds. Whether you’re preparing financial summaries, compiling research data, or managing inventory across sheets, this automation cuts through the clutter. Its ability to handle merged cells—a frequent stumbling block—makes it especially valuable, as it addresses a problem many Excel users face daily.

In conclusion, this VBA code is a practical, SEO-friendly solution for Excel enthusiasts searching for ways to optimize their workflows. By resolving merged cell issues and consolidating scattered data, it exemplifies how automation can turn Excel into a more efficient tool for any task.