🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Pivot Table in Excel — Create, Refresh, and Why It Shows Old Numbers

|

VBA Pivot Table in Excel — Create, Refresh, and Why It Shows Old Numbers

TL;DR — A PivotTable is built on a PivotCache, a frozen copy of the source data. Build one in two steps: PivotCaches.Create(xlDatabase, source) then .CreatePivotTable(destination). Lay out the report by setting each field's .Orientation (xlRowField, xlColumnField, xlPageField) and add numbers with AddDataField. The trap that catches everyone: change the source and the pivot keeps showing the old numbers until you call pt.RefreshTable. Point the cache at a Table and the pivot grows with your data instead of freezing at a fixed range.

Dim pc As PivotCache, pt As PivotTable

Set pc = ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, SourceData:="tblSales")     ' a Table name -> grows on its own
Set pt = pc.CreatePivotTable( _
    TableDestination:=Worksheets("Report").Range("A3"), TableName:="pt_Sales")

pt.PivotFields("Region").Orientation = xlRowField
pt.AddDataField pt.PivotFields("Amount"), "Total Amount", xlSum

pt.RefreshTable                                          ' re-read the source after any change

Everyone who automates reporting eventually writes a macro that builds a PivotTable, and then hits the same wall: the pivot is right the day you build it and wrong every day after. You add a hundred rows of sales, run the report, and the totals have not moved. Nothing errored. The cause is the one idea worth holding onto about pivots: a PivotTable does not summarise your cells — it summarises a PivotCache, a copy of the data made at build time. Every quirk below follows from that: why you refresh, why the source range matters, why two pivots can share one cache. Hold "it reads a snapshot, not the sheet" and the pivot stops surprising you.

What you'll learn

  • The mental model — a pivot reads a PivotCache, not your live cells
  • The modern two-step build: PivotCaches.Create then CreatePivotTable
  • Laying out fields with the four Orientation values, and adding data fields
  • The refresh trap — why your pivot shows old numbers and how to fix it
  • The source-range trap — point the cache at a Table so the pivot grows
  • Finding, repointing, and clearing pivots a workbook has collected

The mental model: a pivot reads a cache, not your cells

When you build a PivotTable, Excel does not wire it to your worksheet. It takes the source data, copies it into a hidden in-memory structure called a PivotCache, and the visible pivot is just a view over that cache. The cache is a photograph taken the moment you built it. Edit the source afterwards and the photograph does not change — which is exactly why the pivot keeps showing old numbers.

Debug.Print pt.PivotCache.SourceData     ' what the cache was built from
Debug.Print pt.PivotCache.RecordCount    ' how many rows the SNAPSHOT holds — not the sheet

Two useful consequences fall straight out of this. First, several pivots can share one cache (build the second from pt.PivotCache instead of a fresh PivotCaches.Create), which keeps the file small and refreshes them together. Second, refreshing is not optional housekeeping — it is the step that re-takes the photograph. Once you see the cache as a separate object with its own copy of the data, the rest of the API stops being a grab-bag of methods and becomes one story: build the cache, shape the view, re-take the snapshot.

Building a PivotTable: cache first, then table

The reliable modern pattern is two explicit steps. Create the cache from the source, then create the table from the cache:

Dim pc As PivotCache, pt As PivotTable

Set pc = ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:="Sales!A1:D1000")

Set pt = pc.CreatePivotTable( _
    TableDestination:=Worksheets("Report").Range("A3"), _
    TableName:="pt_Sales")

SourceData accepts a Table name ("tblSales"), a defined name, or a sheet-qualified address as a string — and the address form is where the source-range trap lives, which we come back to below. The TableDestination must be a real cell on a worksheet, and the pivot needs room to grow, so point it at the top-left of an otherwise empty area. Naming the pivot (TableName:="pt_Sales") matters: it is how you reach it again with Worksheets("Report").PivotTables("pt_Sales") in a later run instead of guessing at PivotTables(1).

You may still see old macros use Worksheets("Report").PivotTableWizard ... in a single call. It works, but it hides the cache, gives you no clean handle on it, and is exactly the code the macro recorder spits out. Prefer the two-step form so the cache — the object that actually holds your data — is something you can name and refresh.

Laying out fields: the four orientations

A PivotTable has four zones, and every field lands in one of them through its .Orientation. Row and column fields become the grid; the page field is the report filter; data fields are the numbers being summarised:

With pt
    .PivotFields("Region").Orientation = xlRowField
    .PivotFields("Month").Orientation = xlColumnField
    .PivotFields("Category").Orientation = xlPageField     ' the report filter
    .AddDataField .PivotFields("Amount"), "Total Amount", xlSum
End With

Two things bite here. First, a field name must match the source header exactly — PivotFields("Ammount") raises a run-time error, not a blank column. Second, and more insidious: the default summary for a data field is Sum only when the whole column is numeric. Slip one text value or one blank into an amount column and Excel silently switches the default to Count, and your report shows "how many" where you expected "how much". That is why adding data fields with an explicit AddDataField ... , xlSum beats dropping them in and hoping — you state the function instead of inheriting a guess. Use xlAverage, xlCount, xlMax, and the rest the same way.

The refresh trap: why your pivot shows old numbers

This is the number-one pivot bug, and it is not a bug at all — it is the cache doing its job. You change the source, the cache still holds the old photograph, and the pivot faithfully shows it:

' You appended 200 rows to the source... the pivot has not noticed.
pt.RefreshTable            ' re-read THIS pivot's cache from its source

pt.PivotCache.Refresh      ' refresh the cache -> updates every pivot sharing it
ThisWorkbook.RefreshAll    ' refresh all pivots, queries and links in the file

RefreshTable re-takes the snapshot for one pivot. If several pivots share a cache, PivotCache.Refresh updates them all at once. The practical rule: any macro that changes the data must refresh the pivots that read it, and any pivot you rely on unattended should refresh when the workbook opens. A pivot that is built once and never refreshed is a screenshot, not a report — it will keep showing the day it was born. If you have ever emailed a "live" dashboard that turned out to be a week stale, this is why.

The source-range trap: point the cache at a Table

The second silent failure is the source range. Build the cache from a fixed address and next month's rows fall outside it — even a refresh will not pull them in, because they are not in the range the cache was told to read:

' FRAGILE — new rows below row 1000 are invisible forever
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, "Sales!A1:D1000")

' ROBUST — a Table auto-expands, so the cache always sees every row
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, "tblSales")

Pointing the cache at a Table (a ListObject) is the fix that makes everything downstream self-maintaining: the Table grows as you add rows, the cache reads the whole Table, and a plain RefreshTable picks up the new data with no range math. A dynamic named range does the same job if you cannot use a Table. To repoint an existing pivot at a better source without rebuilding it, hand it a new cache with ChangePivotCache. See VBA Table for turning a range into a Table and VBA Named Range for the dynamic-name alternative.

Finding, repointing, and clearing pivots

Pivots accumulate, and a later run needs to find the one it made rather than stacking a second copy on top. Loop the collections to locate or clean them:

Dim ws As Worksheet, pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
        Debug.Print ws.Name & " ! " & pt.Name & " -> " & pt.PivotCache.SourceData
    Next pt
Next ws

Before rebuilding, check whether your named pivot already exists and refresh it instead of adding a duplicate; if you genuinely want a fresh one, delete the old with pt.TableRange2.Clear (the whole pivot area) so a new build has clean space. Treat the pivot as a durable object you look up by name — the same discipline that keeps VBA Worksheet code from writing to the wrong tab keeps your reporting macro from breeding pivots on every run.

How ExcelMaster helps

The pivot mistakes that cost real time are quiet ones: the report that never refreshed, the source range that stopped at row 1000 in March, the total that quietly became a count because one cell held text. Each one runs without an error and hands someone the wrong number.

ExcelMaster builds pivots the way a careful analyst would. Ask it to "summarise sales by region and month," and it points the cache at a Table so the source grows on its own, sets each data field's summary function explicitly instead of inheriting Count, refreshes after it writes, and looks the pivot up by name so re-running updates the report rather than duplicating it. You describe the summary you want; it wires the cache, the fields, and the refresh so the number is still right next month.

Frequently asked questions

How do I create a pivot table in VBA?

Build it in two steps. First create a cache from the source: Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="tblSales"). Then create the table from the cache: Set pt = pc.CreatePivotTable(TableDestination:=Worksheets("Report").Range("A3"), TableName:="pt_Sales"). Point SourceData at a Table name so the pivot grows with your data, and give the pivot a name so you can find it again.

Why does my VBA pivot table not update when the data changes?

Because a PivotTable reads a PivotCache — a frozen copy of the source made at build time — not your live cells. Changing the source does not touch the cache, so the pivot keeps showing the old numbers. Call pt.RefreshTable to re-read one pivot, pt.PivotCache.Refresh for every pivot sharing the cache, or ThisWorkbook.RefreshAll for the whole file after any change.

How do I add fields to a pivot table in VBA?

Set each field's Orientation: pt.PivotFields("Region").Orientation = xlRowField, then xlColumnField and xlPageField for the column and filter zones. Add numbers with pt.AddDataField pt.PivotFields("Amount"), "Total Amount", xlSum so you control the summary function — otherwise Excel guesses, and defaults to Count if the column contains any text or blank cell.

How do I stop the pivot source range from missing new rows?

Do not point the cache at a fixed address like "Sales!A1:D1000", because rows added below it are never seen. Convert the source to a Table and pass the Table name as SourceData (PivotCaches.Create(xlDatabase, "tblSales")); the Table auto-expands, so a normal RefreshTable picks up every new row. A dynamic named range works too if a Table is not an option.

How do I loop through all pivot tables in a workbook?

Nest two loops: For Each ws In ThisWorkbook.Worksheets then For Each pt In ws.PivotTables. Inside, pt.Name and pt.PivotCache.SourceData tell you which pivot reads what. Use the same loop to refresh every pivot, or to check whether your named pivot already exists before building a duplicate.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-02.

Related guides: VBA Table · VBA Chart · VBA Range · VBA Named Range · VBA Worksheet