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.
PivotCacherepresents the cached or connected source used by the report. Several PivotTables can share a cache.PivotFieldscontains fields available from the source, such as Region, Product, and Sales.RowFields,ColumnFields, andPageFieldsdescribe fields placed in the corresponding report areas.DataFieldscontains 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
- Data Validation and Quality Assurance in Excel
- Power Query Remove Leading Zeros Without Losing IDs
- Power Query Reference: What It Does and When to Use It
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Loop Only Through Visible Rows?Yes. In Excel VBA, loop through the rows in your data range and check whether each row is…
- VBA Check If Worksheet Exists: Code and ExamplesTo check whether a worksheet exists in VBA, try to set a Worksheet variable from the target workbook’s…
- Excel VBA UserForm Tab Order: Set It CorrectlyTo set an Excel VBA UserForm’s tab order, open the form in the Visual Basic Editor, choose View…
- Autofit VBA Range: Columns, Rows, and ExamplesIn Excel VBA, use the AutoFit method to size worksheet columns or rows to fit their contents. For…
- How to Run Macros in Google Sheets: A Step-by-Step GuideTo run a macro in Google Sheets, open the spreadsheet in a desktop web browser, choose Extensions >…
- UserForm VBA Controls: Types, Events, and ExamplesUserForm VBA controls are the buttons, text boxes, lists, labels, and other interface elements you place on a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.