VBA PivotTable Object: How It Works and How to Use It

VBA & macros · Updated November 18, 2026

In Excel VBA, a PivotTable object represents one PivotTable report in a worksheet: its fields, layout, values, and related settings. You can find it through a worksheet’s PivotTables collection, create it from a PivotCache, and use VBA to arrange fields, refresh results, or automate recurring reports.

What the VBA PivotTable object represents

A PivotTable is the interactive summary shown on a worksheet; the VBA object is the programming interface for that report. For example, a report that totals sales by region is represented by a PivotTable object, even though the source rows and the stored summary data are separate parts of the Excel workbook.

That distinction matters when automating reports. The PivotTable object controls the report’s arrangement and behavior, but it is not the original data and does not, by itself, define where the source data comes from. A PivotTable is associated with a PivotCache, which stores or manages the data used by one or more PivotTables. Refreshing the report updates the cache or report from its source, subject to the source connection and refresh settings.

A worksheet can contain multiple PivotTables. Each has a name, such as SalesPivot, and can be addressed by that name or by its position in the worksheet’s PivotTables collection. A workbook can also contain reports on several worksheets, so code should identify the intended worksheet as well as the PivotTable.

How the object model fits together

The common path through the Excel object model is Workbook → Worksheet → PivotTables collection → PivotTable. The PivotTable then exposes field collections and properties for its layout. For example, code can set a field’s Orientation to xlRowField or xlColumnField, or add a field to the values area with AddDataField.

  • PivotCache represents the cached or connected source used by the report. Several PivotTables can share a cache.
  • PivotFields contains fields available from the source, such as Region, Product, and Sales.
  • RowFields, ColumnFields, and PageFields describe fields placed in the corresponding report areas.
  • DataFields contains fields summarized in the values area, such as a sum of Sales or a count of orders.

These collections are related, but they are not interchangeable. A source field can be available in PivotFields without currently appearing in the report. A field added to the values area is represented in DataFields; setting its summary function or number format is different from positioning a label field in rows or columns.

Getting and inspecting an existing PivotTable

To work with a report that already exists, qualify the worksheet and use the report’s name. For example, ThisWorkbook.Worksheets("Report").PivotTables("SalesPivot") returns the PivotTable named SalesPivot on the Report sheet. Using ThisWorkbook makes the target workbook explicit instead of relying on whichever workbook happens to be active.

You can inspect properties before changing anything. Useful checks include the PivotTable’s name, its location, the number of data fields, and whether a particular source field exists. In production code, handle a missing worksheet or PivotTable deliberately; a misspelled name otherwise causes a runtime error. Avoid relying on collection index 1 when a workbook may have several reports, because inserting or deleting a PivotTable can change collection order.

Field names normally come from the source headers. If the header is Sales Amount, use that field name rather than the worksheet column letter. Source data should have a single header row with nonblank, unique labels. Duplicate or blank headers can prevent a PivotTable from being created or make field references confusing.

Creating a PivotTable with VBA

Creating a report usually involves three decisions: identify a clean source range, create or reuse a PivotCache, and place a PivotTable at a destination cell. The following example assumes the workbook has a worksheet named Data, with headers Region, Product, and Sales in A1:C1 and records directly beneath them. It also assumes there is no existing worksheet named Pivot Report.

The macro creates a new worksheet, makes a PivotCache from the contiguous source data, and adds a report with Region as the row field, Product as the column field, and total Sales as the values field. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z.

Option Explicit

Sub CreateSalesPivot()
    Dim wsData As Worksheet
    Dim wsReport As Worksheet
    Dim src As Range
    Dim dest As Range
    Dim cache As PivotCache
    Dim pt As PivotTable

    ' Assumes Data contains a header row and contiguous records.
    Set wsData = ThisWorkbook.Worksheets("Data")
    Set src = wsData.Range("A1").CurrentRegion

    ' Add a new report sheet; its name must not already exist.
    Set wsReport = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    wsReport.Name = "Pivot Report"

    Set dest = wsReport.Range("A3")
    Set cache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=src.Address(ReferenceStyle:=xlR1C1, External:=True))
    Set pt = cache.CreatePivotTable( _
        TableDestination:=dest, TableName:="SalesPivot")

    With pt
        .PivotFields("Region").Orientation = xlRowField
        .PivotFields("Product").Orientation = xlColumnField
        .AddDataField .PivotFields("Sales"), "Total Sales", xlSum
        .DataFields(1).NumberFormat = "$#,##0.00"
    End With
End Sub

The example uses CurrentRegion, which finds the connected block around A1. A blank row or column inside the source can break that block, so check the data layout if the report omits records. For a source that grows regularly, an Excel Table is often a better-managed source than a fixed range; the cache still needs refreshing after new records are added.

Changing fields, summaries, and layout

After creating a PivotTable, VBA can move fields between report areas by changing each field’s Orientation. For instance, changing a field to xlPageField places it in the filter area. Setting its orientation to xlHidden removes it from the visible layout. A field cannot sensibly occupy multiple layout areas at once, so changing its orientation moves it rather than making a duplicate label field.

For values, AddDataField takes the source field, a caption for the displayed value, and a summary function such as xlSum or xlCount. The appropriate summary depends on the data: summing a numeric Sales field is useful, while counting records may be more suitable for a text identifier. Excel can default to Count when a field contains text or values it cannot treat as numeric, so verify the summary rather than assuming it is Sum.

Number formatting is separate from the summary calculation. Applying a currency number format changes how a total is displayed, not the stored sales values or the calculation itself. Layout choices such as subtotals, grand totals, sorting, filters, and report style can also be controlled through PivotTable properties, but those settings should be applied after the fields have been placed and tested against the workbook’s actual data.

Refreshing data and understanding the cache

A PivotTable does not necessarily update the instant its source cells change. Refreshing recalculates the report using its source and cache. In VBA, PivotTable.RefreshTable refreshes a particular report; PivotCache.Refresh refreshes the cache, which can affect other PivotTables that share it. Excel’s Data > Refresh All command can refresh multiple connections and reports in the workbook.

A refresh cannot compensate for a source range that excludes newly added rows. If a cache was built from A1:C100, adding a record in row 101 does not automatically make that row part of the original fixed address. Use a suitably expanding source, such as an Excel Table, or update the source range before refreshing. External connections may also require credentials, a network connection, or permission to access their data.

Refreshing can change displayed results and may take time with large sources. If an automated process depends on a particular layout, check the results after refresh and avoid assuming that field items or filters will remain unchanged in every data scenario.

Common errors and practical safeguards

  • Field not found: Confirm the header text in the source, including spaces, and make sure the cache includes the header row.
  • Invalid or incomplete source: Check for blank headers, merged header cells, empty rows inside the data, and an incorrect range address.
  • Destination conflict: The destination cell must not overlap another PivotTable or occupied cells that block the report’s expansion.
  • Duplicate names: A worksheet name or PivotTable name may already exist. Choose a unique name or explicitly find and reuse the existing object.
  • Unexpected Count instead of Sum: Inspect the source column for text-formatted numbers, blanks, or other nonnumeric entries, then set and verify the intended summary.
  • Macro does not run: The workbook may be in a macro-disabled format or Excel may block macros under its security settings. Use a macro-enabled workbook format when needed and enable only macros from sources you trust.

For safer automation, qualify workbook and worksheet references, validate the source before creating the report, and avoid deleting or overwriting a report sheet without checking its contents. Test changes on a copy, especially when the macro changes source data or refreshes external connections.

PivotTable object versus PivotCache

The two objects solve different problems. The PivotTable object represents an individual report: its layout, fields, and displayed summary. The PivotCache represents the data source used to build or refresh that report. One cache can serve multiple PivotTables, which can reduce duplicated cached data and let reports use the same source. However, sharing a cache does not make two reports the same object; each can have its own layout and name.

This is also why changing a PivotTable’s field arrangement is not the same as changing its source. If a report is correct but missing recent records, inspect the source and cache. If the records are present but arranged incorrectly, adjust the PivotTable fields and layout.

Frequently Asked Questions

How do I reference a PivotTable in VBA?
Use its worksheet and name, for example ThisWorkbook.Worksheets("Report").PivotTables("SalesPivot"). The worksheet and PivotTable names must match objects in the workbook.

How do I add a field to the values area?
Call the PivotTable’s AddDataField method with a source PivotField, a display caption, and a summary function. For example, a numeric Sales field can be added with xlSum.

How do I refresh a PivotTable with VBA?
Call RefreshTable on the report object to refresh that PivotTable. If you need to refresh a shared cache or multiple workbook connections, choose the appropriate cache or workbook refresh method instead.

Why does VBA say a PivotField cannot be found?
The field name generally must match a source header. Check spelling, spaces, the cache’s source range, and whether the first row of that range contains the expected headers.

Can Excel for the web run PivotTable VBA?
No. Excel for the web can display and work with many PivotTables, but it cannot run or edit VBA macros. Use desktop Excel for Windows or Mac to run VBA; the exact ribbon and editor shortcuts can differ by platform.

Running macros on Windows and Mac

In Microsoft 365 for Windows, open the Visual Basic Editor with Alt+F11, or use Developer > Visual Basic if the Developer tab is available. On Mac, the editor is available in desktop Excel, but the shortcut can depend on the keyboard and function-key settings; use Tools > Macro > Visual Basic Editor if needed. Excel for the web does not provide the VBA editor or run VBA macros.

To preserve VBA code, save the workbook in a macro-enabled format such as .xlsm. A PivotTable can also be created and arranged manually with Insert > PivotTable, so VBA is most useful when the same report must be built, updated, or customized repeatedly. Whether using code or the interface, keep the source headers clear and verify the final summary before sharing it.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.