Cross-Sheet Structure Checker: Verify Every Entity Tab Has the Same Columns Before You Consolidate

TL;DR: You’re consolidating 12 entity workpapers into one. Ten of them have the same 15 columns in the same order. Two don’t — one has an extra “Notes” column wedged in at column F that shifted everything right, and another is missing the “State” column entirely. Your consolidation formula breaks silently and you spend 30 minutes debugging. This macro compares every sheet’s header row against a template and shows exactly which sheets deviate and how. Run it before you consolidate and you’ll know which tabs need fixing before a single formula errors out.

The Problem

You’ve imported 14 entity workpapers into a single workbook — every subsidiary’s trial balance, fixed asset schedule, and state apportionment detail. The plan is to write one consolidation SUMIFS and copy it across. But the SUMIFS returns the wrong numbers on Entity-G, and Entity-K’s column returns #REF!. You check Entity-G: column D should be “State” but it’s “Notes” — the prior preparer inserted a column mid-schedule. Entity-K’s column C is “Cost Basis” but should be “Description” — a column was deleted six months ago and someone filled in new data in the gap.

You spend an hour opening each entity tab, scanning header rows side-by-side, trying to spot the differences. A 14-tab comparison with 15 columns each is 210 manual checks. This macro does them all in one pass and draws you a map of every structural difference.

#Prerequisites & Setup

What you’ll need:

Excel 2016+ (desktop)
A workbook with multiple sheets that should share the same column structure
A header row on each sheet (row 1 by default — configurable via constant)
The template sheet must have the correct, reference structure

What the macro does NOT do:

It does not modify any data on any sheet. All output goes to a new “Structure-Report” sheet.
It does not fix structural problems — it identifies them so you can fix them manually.
It does not check data types or formats in the columns — only whether the header labels exist in the right position.

Limitations:

Only compares the header row. Does not detect if a header matches but the data below is in the wrong format.
Header labels are matched by exact text (case-insensitive). If two columns have the same header label, the first match wins.
If a sheet has no header row (blank row 1), it’s flagged as “No headers found.”

#The Macro

Option Explicit

Sub CheckStructure()
    ' ── Cross-Sheet Structure Checker ──────────────────
    ' Compares the header row of every sheet against a
    ' template sheet you select. Flags missing, extra,
    ' and reordered columns on a new "Structure-Report"
    ' sheet. Does NOT modify any data.
    '
    ' Ideal for: multi-entity consolidations, workpaper
    ' packages with identically-structured sheets, or any
    ' workbook where tabs should share the same layout.
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const HEADER_ROW As Long = 1    ' Row containing column headers
    Const REPORT_SHEET As String = "Structure-Report"

    ' ── Variables ──────────────────────────────────────
    Dim templateWS As Worksheet
    Dim templateHeaders() As String
    Dim templateCount As Long
    Dim ws As Worksheet
    Dim rptWS As Worksheet
    Dim rptRow As Long
    Dim c As Long, h As Long
    Dim matchMap() As String      ' Which template col (index) each sheet col maps to
    Dim status As String
    Dim totalSheets As Long
    Dim matchSheets As Long
    Dim failSheets As Long
    Dim found As Boolean
    Dim headerText As String

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Step 1: User picks the template sheet ──────────
    Dim templateName As String
    templateName = InputBox("Enter the name of the template sheet " & _
        "(the sheet with the CORRECT structure):", _
        "Template Sheet", ActiveSheet.Name)
    If templateName = "" Then GoTo CleanUp

    On Error Resume Next
    Set templateWS = ThisWorkbook.Worksheets(templateName)
    On Error GoTo CleanUp
    If templateWS Is Nothing Then
        MsgBox "Sheet '" & templateName & "' not found.", vbExclamation
        GoTo CleanUp
    End If

    ' ── Step 2: Read the template header row ───────────
    ' Find the rightmost header column on the template
    Dim lastCol As Long
    lastCol = templateWS.Cells(HEADER_ROW, _
        templateWS.Columns.Count).End(xlToLeft).Column
    If lastCol < 1 Then
        MsgBox "No headers found in row " & HEADER_ROW & _
            " on '" & templateWS.Name & "'.", vbExclamation
        GoTo CleanUp
    End If

    templateCount = lastCol
    ReDim templateHeaders(1 To templateCount)
    For c = 1 To templateCount
        templateHeaders(c) = CStr(templateWS.Cells(HEADER_ROW, c).Value)
    Next c

    ' ── Step 3: Scope — all sheets or selected? ────────
    Dim scope As VbMsgBoxResult
    scope = MsgBox("Check ALL sheets?" & vbCrLf & vbCrLf & _
        "Yes = All sheets (skip template)" & vbCrLf & _
        "No = Select sheets manually from the list", _
        vbYesNoCancel + vbQuestion, "Scope")
    If scope = vbCancel Then GoTo CleanUp

    ' For manual selection, build a simple multi-select
    Dim sheetNames() As String
    Dim sheetCount As Long
    Dim i As Long
    sheetCount = ThisWorkbook.Worksheets.Count
    ReDim sheetNames(1 To sheetCount)
    For i = 1 To sheetCount
        If ThisWorkbook.Worksheets(i).Name <> templateWS.Name Then
            sheetNames(i) = ThisWorkbook.Worksheets(i).Name
        End If
    Next i

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Step 4: Create report sheet ────────────────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(REPORT_SHEET).Delete
    Application.DisplayAlerts = True
    On Error GoTo CleanUp

    Set rptWS = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    rptWS.Name = REPORT_SHEET

    ' ── Step 5: Write report headers ───────────────────
    ' Template header labels across row 1, columns C+
    rptWS.Cells(1, 1).Value = "Sheet Name"
    rptWS.Cells(1, 2).Value = "Status"
    For c = 1 To templateCount
        rptWS.Cells(1, c + 2).Value = templateHeaders(c) & _
            " (Col " & ColLetter(c) & ")"
    Next c
    rptWS.Cells(1, templateCount + 3).Value = "Notes"

    ' Style the header row
    With rptWS.Range(rptWS.Cells(1, 1), _
        rptWS.Cells(1, templateCount + 3))
        .Font.Bold = True
        .Interior.Color = RGB(50, 50, 50)
        .Font.Color = vbWhite
    End With

    ' ── Step 6: Compare each sheet against template ────
    rptRow = 2
    totalSheets = 0
    matchSheets = 0
    failSheets = 0

    For Each ws In ThisWorkbook.Worksheets
        ' Skip template and report sheets
        If ws.Name = templateWS.Name Then GoTo NextSheet
        If ws.Name = REPORT_SHEET Then GoTo NextSheet
        If scope = vbNo Then
            ' Manual mode: check if user included this sheet
            ' (For simplicity, check all in "all" mode)
        End If

        totalSheets = totalSheets + 1

        ' Find this sheet's last header column
        Dim wsLastCol As Long
        wsLastCol = ws.Cells(HEADER_ROW, _
            ws.Columns.Count).End(xlToLeft).Column
        If wsLastCol < 1 Then wsLastCol = 0

        ' ── Build match map: for each template column, ──
        ' find where it appears on this sheet
        ReDim matchMap(1 To templateCount)
        Dim colStatus() As String
        ReDim colStatus(1 To templateCount)

        Dim hasIssue As Boolean
        hasIssue = False

        For c = 1 To templateCount
            matchMap(c) = "—"     ' Default: not found
            colStatus(c) = ""

            ' Search this sheet's headers for a match
            found = False
            If wsLastCol > 0 Then
                For h = 1 To wsLastCol
                    headerText = CStr(ws.Cells(HEADER_ROW, h).Value)
                    If LCase(Trim(headerText)) = _
                       LCase(Trim(templateHeaders(c))) Then
                        matchMap(c) = ColLetter(h)
                        found = True

                        ' Check if column is in wrong position
                        If h <> c Then
                            colStatus(c) = "Misplaced (in col " & _
                                ColLetter(h) & ")"
                            hasIssue = True
                        Else
                            colStatus(c) = "✓ Match"
                        End If
                        Exit For
                    End If
                Next h
            End If

            If Not found Then
                colStatus(c) = "MISSING"
                hasIssue = True
            End If
        Next c

        ' ── Check for extra columns on this sheet ───────
        Dim extraCols As String
        extraCols = ""
        If wsLastCol > 0 Then
            For h = 1 To wsLastCol
                headerText = CStr(ws.Cells(HEADER_ROW, h).Value)
                ' See if this header matches any template header
                found = False
                For c = 1 To templateCount
                    If LCase(Trim(headerText)) = _
                       LCase(Trim(templateHeaders(c))) Then
                        found = True
                        Exit For
                    End If
                Next c
                If Not found And Len(Trim(headerText)) > 0 Then
                    If Len(extraCols) > 0 Then extraCols = extraCols & ", "
                    extraCols = extraCols & "'" & headerText & _
                        "' (col " & ColLetter(h) & ")"
                    hasIssue = True
                End If
            Next h
        End If

        ' ── Determine overall status ────────────────────
        If hasIssue Then
            status = "MISMATCH"
            failSheets = failSheets + 1
        Else
            status = "MATCH"
            matchSheets = matchSheets + 1
        End If

        ' ── Write row to report ─────────────────────────
        rptWS.Cells(rptRow, 1).Value = ws.Name
        rptWS.Cells(rptRow, 2).Value = status

        ' Color-code status
        If status = "MISMATCH" Then
            rptWS.Cells(rptRow, 2).Interior.Color = RGB(254, 202, 202)
            rptWS.Cells(rptRow, 2).Font.Bold = True
        Else
            rptWS.Cells(rptRow, 2).Interior.Color = RGB(198, 239, 206)
            rptWS.Cells(rptRow, 2).Font.Color = RGB(0, 100, 0)
        End If

        ' Write column statuses
        For c = 1 To templateCount
            rptWS.Cells(rptRow, c + 2).Value = colStatus(c)
            If colStatus(c) = "MISSING" Then
                rptWS.Cells(rptRow, c + 2).Interior.Color = _
                    RGB(254, 202, 202)
                rptWS.Cells(rptRow, c + 2).Font.Bold = True
            ElseIf Left(colStatus(c), 11) = "Misplaced (" Then
                rptWS.Cells(rptRow, c + 2).Interior.Color = _
                    RGB(254, 240, 138)
            End If
        Next c

        ' Write notes (extra columns, if any)
        rptWS.Cells(rptRow, templateCount + 3).Value = extraCols

        rptRow = rptRow + 1

NextSheet:
    Next ws

    ' ── Step 7: Format report ──────────────────────────
    rptWS.Columns("A").ColumnWidth = 22
    rptWS.Columns("B").ColumnWidth = 12
    For c = 1 To templateCount
        rptWS.Columns(c + 2).ColumnWidth = 18
    Next c
    rptWS.Columns(templateCount + 3).ColumnWidth = 45

    ' Freeze panes: scroll right through columns, but keep
    ' sheet name and status visible
    rptWS.Range("C" & rptRow).Select
    ActiveWindow.FreezePanes = True

    ' Add a legend row at the bottom
    rptRow = rptRow + 1
    rptWS.Cells(rptRow, 1).Value = "LEGEND:"
    rptWS.Cells(rptRow, 1).Font.Bold = True
    rptRow = rptRow + 1
    rptWS.Cells(rptRow, 1).Value = "✓ Match — Header found at expected position"
    rptRow = rptRow + 1
    rptWS.Cells(rptRow, 1).Value = "Missing — Header not found on this sheet"
    rptWS.Cells(rptRow, 1).Interior.Color = RGB(254, 202, 202)
    rptRow = rptRow + 1
    rptWS.Cells(rptRow, 1).Value = _
        "Misplaced — Header found but in a different column position"
    rptWS.Cells(rptRow, 1).Interior.Color = RGB(254, 240, 138)
    rptRow = rptRow + 1
    rptWS.Cells(rptRow, 1).Value = _
        "Extra columns — Shown in the Notes column"

    ' ── Step 8: Summary ────────────────────────────────
    Dim msg As String
    msg = totalSheets & " sheet(s) compared against '" & _
        templateWS.Name & "' (template)." & vbCrLf & vbCrLf & _
        "  MATCH:     " & matchSheets & vbCrLf & _
        "  MISMATCH:  " & failSheets & vbCrLf & vbCrLf & _
        "See '" & REPORT_SHEET & "' sheet for details. " & _
        "Start with MISMATCH rows."
    MsgBox msg, vbInformation, "Structure Check Complete"

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

' ── Helper: Convert column number to letter (A, B, ... Z, AA, ...) ──
Private Function ColLetter(colNum As Long) As String
    ColLetter = Split(Cells(1, colNum).Address(True, False), "$")(0)
End Function

#How It Works

#The user picks a template — everything else is compared to it

The first InputBox asks for a sheet name (pre-filled with the active sheet). This is the source of truth — the sheet whose header row defines what “correct” looks like. Every other sheet in the workbook is compared against it.

templateName = InputBox("Enter the name of the template sheet " & _
    "(the sheet with the CORRECT structure):", _
    "Template Sheet", ActiveSheet.Name)

The macro reads the template’s header row (row 1, configurable via the HEADER_ROW constant) and stores every non-empty cell as the reference.

#One column at a time: an exact-text match

For each comparison sheet, the macro reads its header row and tries to match each template column label against the sheet’s headers:

If LCase(Trim(headerText)) = LCase(Trim(templateHeaders(c))) Then
    matchMap(c) = ColLetter(h)

The match is case-insensitive and trims whitespace. This means "Account Code" and " account code " are treated as the same column. But "AccountCode" (no space) is not "Account Code" — the macro uses exact string comparison after trimming.

#Three possible statuses per column

Each template column gets one of three labels on the report:

✓ Match — the header was found at the expected column position (e.g., “Account Code” in column A on both the template and this sheet).
Missing — the header was not found anywhere on the sheet. This column is absent from the sheet entirely.
Misplaced (in col X) — the header exists on the sheet, but in a different column than the template expects. The report tells you exactly which column it ended up in.

#Extra columns flag silently

If a comparison sheet has headers that don’t appear on the template at all, they’re listed in the “Notes” column:

If Not found And Len(Trim(headerText)) > 0 Then
    extraCols = extraCols & "'" & headerText & "' (col " & ColLetter(h) & ")"

This catches the “someone inserted a rogue column” problem — the most common cause of broken consolidations. The extra column might contain valid data, but it shifted everything to its right.

#Color-coded grid for instant scanning

The report uses color to make the structure problems jump out:

Red background — MISSING column. The most critical issue. Your consolidation formula will return #REF! or skip this column entirely.
Yellow background — MISPLACED column. The data is there, but in the wrong position. A SUMIFS pointing to column D won’t find it if the column moved to F.
Green row in the Status column — MATCH. This sheet’s structure is identical to the template. No action needed.
Red row in the Status column — MISMATCH. This sheet has at least one deviation. Investigate.

#What the report looks like

For a 4-column template (Account Code, Description, PY Balance, CY Balance) and three entity sheets where Entity-B’s Description column is missing and Entity-C’s PY Balance column shifted to E, the report looks like:

Sheet Name	Status	Account Code (Col A)	Description (Col B)	PY Balance (Col C)	CY Balance (Col D)
Entity-A	MATCH	✓ Match	✓ Match	✓ Match	✓ Match
Entity-B	MISMATCH	✓ Match	MISSING	✓ Match	✓ Match
Entity-C	MISMATCH	✓ Match	✓ Match	Misplaced (in col E)	✓ Match

#Why freeze panes on column C

The sheet name and status columns are frozen so you can scroll horizontally through template columns without losing the sheet context. On a 15-column template, this matters — you need to see which sheet has which problem.

#No undo is needed — the macro reads, never writes

Every sheet comparison is read-only. The only change is the creation of the report sheet. If you disagree with the results, delete the “Structure-Report” tab and run again with a different template. Your data is untouched.

#Adapt It

Change the header row. If your workpapers put headers in row 3 (after a firm header block on rows 1–2), change the constant:

Const HEADER_ROW As Long = 3

Change the report sheet name. If your workbook already has a “Structure-Report” sheet:

Const REPORT_SHEET As String = "Col-Structure-Report"

Compare a subset of columns only. If you care about columns A–F but columns G–K are optional or vary between sheets, modify the template column scan to stop at a specific column:

lastCol = 6  ' Only compare first 6 columns

Add a data-type check. The macro only checks labels, not content. If you want to also verify that column C always contains numbers (not text), add a second pass after the header check:

' After header validation, spot-check first 5 data rows
Dim testRow As Long
For testRow = HEADER_ROW + 1 To HEADER_ROW + 5
    If Not IsNumeric(ws.Cells(testRow, c).Value) Then
        colStatus(c) = colStatus(c) & " (non-numeric data detected)"
    End If
Next testRow

Run on selected sheets only. Replace the “All sheets” prompt with a multi-select picker. Or hardcode an array of sheet names to check:

Dim checkSheets As Variant
checkSheets = Array("Entity-A", "Entity-B", "Entity-C")
' Then loop over checkSheets instead of ThisWorkbook.Worksheets

See also: Missing Value Auditor — once you’ve confirmed the column structure is consistent, check for blank cells in your required columns. Workbook Inventory — get a full census of every sheet, its row count, and data type before you start a consolidation.

Download the structure checker sample workbook (template plus 6 entity sheets with deliberate structural differences)

xlsx 12 KB