Wauvel

Excel like a finance pro.

← All VBA snippets

Add up the same cell across every sheet

Twelve monthly tabs with the same layout, one total — and a warning when one tab isn't the same.

1What it does

A workbook with a tab per month is the most common structure in small-business finance, and the most common question of it is "what's the year so far?" This loops every worksheet, reads the same cell from each, and writes the total to a summary. The part that makes it worth a macro rather than a 3-D formula is the check: it confirms each tab's label in column A matches before it adds the number, so a month where someone inserted a row gets flagged instead of quietly contributing the wrong figure.

2The code

Sub ConsolidateMonths()
    Const LABEL_CELL As String = "A12"      ' should say "Revenue" on every tab
    Const VALUE_CELL As String = "B12"
    Const EXPECTED As String = "Revenue"

    Dim ws As Worksheet, total As Double, used As Long
    Dim skipped As String

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Summary" Then
            ' Check the row is what we think it is BEFORE trusting the number.
            If Trim$(ws.Range(LABEL_CELL).Value) = EXPECTED Then
                If IsNumeric(ws.Range(VALUE_CELL).Value) Then
                    total = total + ws.Range(VALUE_CELL).Value
                    used = used + 1
                Else
                    skipped = skipped & vbNewLine & ws.Name & " — not a number"
                End If
            Else
                skipped = skipped & vbNewLine & ws.Name & " — " & LABEL_CELL & _
                          " says '" & ws.Range(LABEL_CELL).Value & "'"
            End If
        End If
    Next ws

    With ThisWorkbook.Worksheets("Summary")
        .Range("B2").Value = total
        .Range("B3").Value = used & " sheets included"
    End With

    If Len(skipped) > 0 Then
        MsgBox "Totalled " & used & " sheets. Left out:" & skipped, vbExclamation
    End If
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾
  1. 1

    Don't see the Developer tab in the ribbon?

    You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
    • Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
    • Mac: Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
  2. 2

    Paste in the code

    Press Alt + F11 to open the Visual Basic editor, then Insert → Module and paste the snippet's code into the blank window. Close it with Alt + Q.
  3. 3

    Run it

    Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
  4. 4

    Keep the macro — save as .xlsm

    File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
  5. 5

    Macros blocked?

    Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

  • Year-to-date revenue from twelve monthly tabs.
  • Sum a department's spend across one tab per cost centre.
  • Check every tab has the same structure before a consolidation you trust.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

What B12 holds on each tab
Jan — A12 says "Revenue"$41,000
Feb — A12 says "Revenue"$38,500
Mar — A12 says "Revenue"$44,100
Apr — A12 says "Revenue"$39,700

The label check is the reason this is a macro and not =SUM(Jan:Dec!B12). Toggle the broken tab, then run it.

5Pitfalls

Someone inserted a row on one tab, so B12 is now a different line — and the total is silently wrong.

Fix: Check the LABEL cell before reading the value, as above. That check is the whole reason to use a macro.

The Summary sheet adds itself into the total.

Fix: Exclude it by name, or loop only sheets matching a pattern like Jan–Dec.

A cell holding text like "n/a" raises a type mismatch mid-loop.

Fix: IsNumeric before adding, and report what was skipped rather than stopping.

Hidden or chart sheets break the loop.

Fix: Worksheets (not Sheets) skips chart sheets. Test ws.Visible if hidden tabs should be excluded.

6No-code alternatives

  • A 3-D reference — =SUM(Jan:Dec!B12) needs no code and is fine when you trust every tab's layout. It has no way to notice when one tab differs.
  • Power Query — Better when the tabs are really tables — it appends them and refreshes without code.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.