Wauvel

Excel like a finance pro.

← All VBA snippets

Build a pivot table in code

A department-by-month pivot, rebuilt from scratch every month in one run.

1What it does

Refreshing a pivot someone else built is one line. Building one from a raw export each month is where people spend real time: create the cache, place the table, drag four fields into position, set the number format, sort. The code for it is verbose but completely mechanical — a PivotCache from the source range, a PivotTable from the cache, then an orientation for each field. Worth having when the export's range changes every month, because a pivot built fresh can never be pointing at last month's range.

2The code

Sub BuildDeptMonthPivot()
    Dim src As Worksheet, out As Worksheet
    Dim pc As PivotCache, pt As PivotTable, lastRow As Long

    Set src = ThisWorkbook.Worksheets("Data")
    lastRow = src.Cells(src.Rows.Count, 1).End(xlUp).Row
    If lastRow < 2 Then Exit Sub

    ' Rebuild the output sheet so an old pivot can't linger.
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Worksheets("Pivot").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Set out = ThisWorkbook.Worksheets.Add
    out.Name = "Pivot"

    ' The cache is built from THIS month's range, so it can't be stale.
    Set pc = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=src.Range("A1:C" & lastRow))

    Set pt = pc.CreatePivotTable( _
        TableDestination:=out.Range("A3"), TableName:="ptDeptMonth")

    With pt
        .PivotFields("Department").Orientation = xlRowField
        .PivotFields("Month").Orientation = xlColumnField
        With .AddDataField(.PivotFields("Amount"), "Total amount", xlSum)
            .NumberFormat = "#,##0;(#,##0)"
        End With
        .RowAxisLayout xlTabularRow
    End With
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

  • Rebuild a monthly department summary from a fresh export.
  • Produce the same pivot layout every month without dragging fields.
  • Generate one pivot per region from a list of source sheets.

4See it in action

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

Data — October's export, one row longer than September's
Sales · Sep41,000
Sales · Oct38,500
Marketing · Sep12,400
Marketing · Oct15,100
Operations · Oct27,300

Cache from the current range, table from the cache, then an orientation per field. Verbose, but entirely mechanical.

5Pitfalls

The pivot points at last month's source range, so new rows never appear.

Fix: Build the cache from the current last row each run, as above — that's the reason to do this in code.

PivotFields("Amount") errors with 1004 because the header is "Amount " with a space.

Fix: Field names must match the header text exactly. Trim the headers in the source first.

A second run fails because a pivot called ptDeptMonth already exists.

Fix: Delete and rebuild the output sheet, as above, or clear the old pivot first.

Amounts are counted rather than summed.

Fix: A column with any blank or text cell defaults to Count. Specify xlSum explicitly, as above.

6No-code alternatives

  • A pivot built once, plus a dynamic source — Point the pivot at an Excel Table and it grows with the data — no rebuild needed. Often the better answer.
  • GROUPBY / PIVOTBY — The same rollup as a formula, with nothing to refresh and nothing to rebuild.

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.