VBA Loop Through a PivotTable: Examples and Tips

To loop through a PivotTable in Excel VBA, assign it to a PivotTable variable and use a For Each loop over the collection you need: PivotTables on a sheet, fields in a PivotTable, items in a field, or cells in its data area. The right collection depends on whether you want to inspect the report structure, read its values, or change its filters.

How PivotTable loops work

Excel exposes PivotTables through several nested collections. A worksheet has a PivotTables collection; a PivotTable has PivotFields; and each field can expose PivotItems. A PivotTable also provides ranges such as TableRange2 and DataBodyRange. In VBA, a For Each loop visits each object in one of these collections in turn.

For example, looping through fields is different from looping through the displayed cells. A field loop can include items that are currently filtered out, while a cell loop follows the report’s current layout. Choose the collection that matches the task instead of treating a PivotTable as an ordinary rectangular range.

The examples below use Excel for Microsoft 365 on Windows. Before running any macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z. Save a macro-enabled workbook as .xlsm if you need to retain its code.

Loop through every PivotTable in a workbook

Start with a worksheet’s PivotTables collection. This example finds every PivotTable in the workbook, prints its worksheet and name, and reports the address of its full report range in the Immediate window. Open that window in the Visual Basic Editor with Ctrl+G.

Sub ListPivotTables()
    Dim ws As Worksheet
    Dim pt As PivotTable

    ' Visit every PivotTable on every worksheet.
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            Debug.Print ws.Name, pt.Name, pt.TableRange2.Address
        Next pt
    Next ws
End Sub

TableRange2 includes the entire PivotTable report, including report-filter areas when present. By contrast, TableRange1 excludes page fields. Use the former when you need the full displayed report footprint, and check which range is appropriate before selecting, copying, or formatting cells.

If the task concerns just one report, get it directly rather than searching every sheet. For a worksheet named Summary with a PivotTable named SalesPivot, use Set pt = ThisWorkbook.Worksheets("Summary").PivotTables("SalesPivot"). PivotTable names are not necessarily the same as their source-field names or the worksheet name.

Loop through fields and their items

Use PivotFields to inspect a report’s fields and PivotItems to inspect the items in one field. The following example prints the item names in the Region field of a named PivotTable. It assumes the field is actually named Region in the PivotTable’s field list.

Sub ListRegionItems()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem

    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("SalesPivot")
    Set pf = pt.PivotFields("Region")

    ' Print each item name in the field.
    For Each pi In pf.PivotItems
        Debug.Print pi.Name
    Next pi
End Sub

Items can include (blank), and their order can reflect the PivotTable’s sorting rather than alphabetical order. Also, a PivotField collection contains fields used in different parts of the report, not only row labels. To check a field’s role, inspect its Orientation property; possible orientations include row, column, page, and data fields.

Field captions can differ from source column headings. A value field might be displayed as Sum of Sales, for example, while its source field is named Sales. If a field lookup fails, inspect the PivotTable’s field names and captions rather than assuming the visible label is the source name.

Read values from the PivotTable data area

When the goal is to process the displayed results, loop through DataBodyRange. It represents the value cells in the report, rather than its row and column headings. This example prints each value and its cell address. It checks whether the range exists because a report without a data area has no cells to process.

Sub ListPivotValues()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim cell As Range

    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("SalesPivot")

    ' DataBodyRange can be Nothing if there is no values area.
    If Not pt.DataBodyRange Is Nothing Then
        For Each cell In pt.DataBodyRange.Cells
            Debug.Print cell.Address, cell.Value
        Next cell
    End If
End Sub

This reads the values currently displayed, which may be totals or subtotals as well as detail intersections. It does not identify which row or column labels belong to each value. If your next step needs that context, inspect the report’s row and column areas too, or use the PivotTable’s layout and cell positions deliberately. Avoid assuming that a particular cell address will always represent the same category: filters, sorting, and refreshed source data can move results.

Choose the right range for the task

Three common choices serve different purposes:

DataBodyRange covers the PivotTable’s value cells. Use it to read or format the values area.
TableRange1 covers the report but excludes report-filter fields above it.
TableRange2 covers the report including report-filter fields, if present.

For example, to apply number formatting only to calculated values, use DataBodyRange rather than formatting TableRange2. To copy the visible report including its headings, a table range may be more suitable. Before using a range property, consider what happens when the PivotTable has no data, or when its size changes after a refresh.

Looping through a range cell by cell is useful for conditional processing, but it is often slower than working with a whole range at once. If all you need is to apply a format uniformly, assigning a format to the appropriate range is usually simpler than iterating through every cell.

Change filters or refresh a PivotTable

A common misconception is that looping through PivotItems automatically changes the report’s filter. The loop only visits the items. To filter a report, you must set the relevant field or item properties, and the correct approach depends on whether the field is a report filter, a row or column field, or part of an OLAP-based data model.

For a conventional, non-OLAP PivotTable where Region is placed in the report-filter area, CurrentPage selects one page item. Check that the field is a page field and that the requested item exists before assigning it. Hiding items one by one in a row or column field requires more care: Excel generally requires at least one item to remain visible, and changing visibility can fail when the requested state would leave none. OLAP and Data Model PivotTables have additional object-model constraints, so do not assume code written for a conventional PivotTable will work unchanged.

When the source data has changed, refreshing the report is a separate operation from looping over it. A PivotTable can be refreshed with its RefreshTable method, while refreshing a cache or all workbook connections is a broader task. PivotTables can share a PivotCache, so a cache refresh may affect more than one report. Decide whether the change is to the source, the cache, or only the displayed report before choosing a refresh method.

Common errors and ways to prevent them

Most loop problems come from an incorrect object reference or an assumption about the PivotTable’s layout. Use these checks when adapting an example:

Subscript out of range: confirm the worksheet and PivotTable names. They must match the workbook exactly.
Unable to get the PivotFields property: check the field name and whether the report is a conventional PivotTable or an OLAP/Data Model report.
No values are processed: confirm that the PivotTable has a values area and that DataBodyRange is not Nothing.
Unexpected items appear: items in the field collection may include filtered-out entries or blanks, not only currently visible labels.
A filter assignment fails: verify the field orientation, item name, and PivotTable type. A page-field property is not interchangeable with row-item visibility.

For troubleshooting, print ws.Name, pt.Name, and the field names to the Immediate window before adding changes. Test on a copy, particularly when a macro hides items, changes filters, or refreshes a shared cache. A small diagnostic loop is easier to inspect than a macro that changes many reports at once.

When a VBA loop is the right tool

VBA is useful when a task must be repeated across many PivotTables, when a report needs to be processed after refresh, or when a workflow requires custom actions that standard PivotTable commands do not provide. For a one-time review, clicking through the field list or copying a report may be faster and easier to audit.

For analysis that can be expressed as a PivotTable calculation, calculated fields, value summaries, filters, or slicers may be easier to maintain than a macro. Power Query can help prepare and reshape source data before it reaches a PivotTable. Excel formulas can also refer to PivotTable results in some situations, though a formula-based approach may need adjustment if report layout changes. Choose VBA when automation is genuinely useful, not merely because the output happens to be a PivotTable.

Frequently Asked Questions

How do I loop through all PivotTables on one worksheet?
Declare a PivotTable variable and use For Each pt In Worksheets("Summary").PivotTables. Replace Summary with the sheet name, and use pt.Name or pt.TableRange2.Address to inspect each report.

How do I loop through only visible PivotItems?
Iterate through the field’s PivotItems and test each item’s Visible property in a conventional PivotTable. Visibility handling can differ or be restricted in OLAP and Data Model PivotTables, so confirm the report type before relying on that property.

What is the difference between DataBodyRange and TableRange2?
DataBodyRange is the values area. TableRange2 is the entire PivotTable report, including report-filter fields when they exist. Use the range that matches the cells you intend to read or change.

Can I loop through PivotTable cells with For Each?
Yes. For example, loop through pt.DataBodyRange.Cells to visit value cells. Check that DataBodyRange is not Nothing first, and remember that the values area can include subtotals and grand totals.

Does the macro work in Excel for the web?
Excel for the web can open workbooks containing VBA, but it cannot create, run, or edit VBA macros. Use desktop Excel for Windows to run these examples. Excel for Mac supports VBA, although some object-model behavior and available features may differ from Windows.

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.