How Excel Sees a PivotTable

Three objects, not one

When you build a PivotTable by hand it feels like a single thing. In code it is three, and knowing which is which removes most of the confusion:

• PivotCache — an invisible copy of the source data, held in memory inside the workbook. This is what makes a pivot fast, and why the report does not change when you edit the source until you refresh.
• PivotTable — the visible report on the sheet. It reads from a cache and knows nothing about the original range.
• PivotField — one column of the source, placed in the rows, columns, values or filter area.

Caches belong to the workbook, PivotTables to a worksheet, fields to a PivotTable. Several PivotTables can share one cache, which keeps the file small and lets one refresh update them all — and causes the side effect people find baffling: group a date field in one pivot and the grouping appears in the other, because grouping is stored in the cache, not in the report.

First, something to look at

Run this once. It creates a small source table and one PivotTable, so the audit macro below has something to report on. Its lines are unpacked properly in lessons 2 to 4.

Option Explicit

Sub MakePivotDemo()
    Dim wb As Workbook
    Dim wsData As Worksheet
    Dim wsRep As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable
    Dim tag As String
    Dim r As Long

    Set wb = ThisWorkbook
    tag = Format(Now, "hhmmss")

    Set wsData = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
    wsData.Name = "Demo Data " & tag
    wsData.Range("A1:C1").Value = Array("Region", "Product", "Amount")

    For r = 2 To 25
        wsData.Cells(r, 1).Value = Choose((r Mod 3) + 1, "North", "South", "West")
        wsData.Cells(r, 2).Value = Choose((r Mod 2) + 1, "Cable", "Adapter")
        wsData.Cells(r, 3).Value = 100 + r * 7
    Next r

    Set wsRep = wb.Worksheets.Add(After:=wsData)
    wsRep.Name = "Demo Report " & tag

    Set pc = wb.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=wsData.Range("A1").CurrentRegion)

    Set pt = pc.CreatePivotTable( _
        TableDestination:=wsRep.Range("A3"), _
        TableName:="ptDemo" & tag)

    pt.PivotFields("Region").Orientation = xlRowField
    pt.PivotFields("Product").Orientation = xlColumnField
    pt.AddDataField pt.PivotFields("Amount"), "Total", xlSum
End Sub

The macro: an inventory of every pivot in the workbook

This is the one worth keeping. Hand it a workbook full of pivots somebody else built and it writes out, on one sheet, where each report lives, which cache it reads, how many records that cache holds, when it was last refreshed and where every field sits.

Sub AuditPivotTables()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wsOut As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim placement As String
    Dim nextRow As Long

    Set wb = ThisWorkbook

    On Error Resume Next
    Set wsOut = wb.Worksheets("Pivot Audit")
    On Error GoTo 0

    If wsOut Is Nothing Then
        Set wsOut = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
        wsOut.Name = "Pivot Audit"
    End If

    wsOut.Cells.Clear
    wsOut.Range("A1:G1").Value = Array("Sheet", "PivotTable", "Cache", _
        "Records in cache", "Last refresh", "Field", "Sits in")
    wsOut.Range("A1:G1").Font.Bold = True
    nextRow = 2

    For Each ws In wb.Worksheets
        For Each pt In ws.PivotTables
            For Each pf In pt.PivotFields
                Select Case pf.Orientation
                    Case xlRowField
                        placement = "Rows"
                    Case xlColumnField
                        placement = "Columns"
                    Case xlDataField
                        placement = "Values"
                    Case xlPageField
                        placement = "Filter"
                    Case Else
                        placement = "not used"
                End Select

                wsOut.Cells(nextRow, 1).Value = ws.Name
                wsOut.Cells(nextRow, 2).Value = pt.Name
                wsOut.Cells(nextRow, 3).Value = pt.CacheIndex
                wsOut.Cells(nextRow, 4).Value = pt.PivotCache.RecordCount
                wsOut.Cells(nextRow, 5).Value = pt.RefreshDate
                wsOut.Cells(nextRow, 6).Value = "[" & pf.SourceName & "]"
                wsOut.Cells(nextRow, 7).Value = placement
                nextRow = nextRow + 1
            Next pf
        Next pt
    Next ws

    If nextRow = 2 Then
        wsOut.Range("A2").Value = "No PivotTables in this workbook."
    End If

    wsOut.Columns("E").NumberFormat = "yyyy-mm-dd hh:mm"
    wsOut.Columns("A:G").AutoFit
    wsOut.Activate
End Sub

Line by line

• The three nested For Each loops are the object model itself: worksheets hold PivotTables, PivotTables hold PivotFields. Only the caches sit elsewhere, on the workbook.
• pt.CacheIndex is the number of the cache this report reads. Two pivots showing the same number share one cache — they refresh together and share any grouping.
• pt.PivotCache.RecordCount is how many rows the snapshot holds. Fewer records than the source has rows is the classic sign of a pivot built over a range that has since grown.
• pt.RefreshDate is when that snapshot was taken. A pivot never hides that it is stale; nobody ever looks.
• pf.Orientation says where a field sits. Unused fields report xlHidden, hence the "not used" rows — a column exists in the cache whether the report shows it or not.
• pf.SourceName is the column name in the source, pf.Name the caption in the report. For a value field the two differ: Amount against Sum of Amount.
• The square brackets are deliberate: they make a trailing space visible, which is the point of the next section.
• If nextRow never moved, the workbook has no pivots and the macro says so.

The trap: "Unable to get the PivotFields property"

Run-time error 1004 on a line like pt.PivotFields("Amount").Orientation = xlDataField hardly ever means the field is missing. It means the name is not exactly Amount. Two everyday causes: the header cell in the source ends in a space nobody can see; or the field is already in the values area, where its name has become Sum of Amount while SourceName stays Amount. On screen the two cases look identical.

' Ask the pivot what it actually has, instead of guessing
Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveSheet.PivotTables(1)

For Each pf In pt.PivotFields
    Debug.Print "[" & pf.SourceName & "] is shown as [" & pf.Name & "]"
Next pf

Cure it at the source: trim the header row before building the cache, and reach value fields through pt.DataFields. The audit macro is the same idea at workbook scale — when a pivot script fails on a file you did not write, run the audit first and read the real names off the sheet.

How this course goes on

This lesson only reads pivots; the next three build one. Lesson 2 creates the cache and shows why a Table beats a fixed range as its source, lesson 3 turns a cache into a report on a sheet, lesson 4 places fields into the rows, columns and values areas. After that come summary functions and number formats, filters and slicers, and the rebuild patterns that stop a monthly report breaking the month the data grows.