Can Excel VBA Read Data From a PivotTable?

VBA & macros · Updated November 18, 2026

Yes. Excel VBA can read a PivotTable’s values and labels through the PivotTable object model, using methods such as GetPivotData or ranges such as DataBodyRange. Use GetPivotData when you want a particular value identified by its field and item; use a range when you need to process the displayed cells in bulk.

What VBA reads when it reads a PivotTable

A PivotTable is a report built from source data and managed by Excel. VBA can work with the report currently displayed on a worksheet: its value cells, row and column labels, fields, filters, and layout. The code does not have to reconstruct the aggregation from the original records just to retrieve a total shown in the report.

That distinction matters because a PivotTable is not simply a fixed block of cells. Its size and arrangement can change when someone filters it, expands a hierarchy, adds a field, or refreshes it. A reference to a particular address such as D7 might point to West-region sales today and a different item after the layout changes. PivotTable-aware methods can make the intent clearer and more resilient.

VBA can also read the worksheet cells that display the PivotTable. Those cells are useful when you need to copy or inspect the whole report, but their positions depend on the current layout. The best approach depends on whether your goal is to retrieve a defined business value or to process the visible report as a grid.

Two common ways to retrieve PivotTable data

  • Use GetPivotData for a specific result. Request a value by naming the data field and the relevant PivotTable fields and items. This is usually the clearest choice for a result such as total sales for the West region.
  • Use a range for a group of displayed cells. DataBodyRange refers to the value area, while TableRange1 and TableRange2 refer to broader parts of the report. A range is useful for copying or iterating through many cells.

Both approaches read the PivotTable as it exists in the workbook. If its source data has changed but the report has not been refreshed, the displayed result may be out of date. Decide whether the macro should refresh first, and make sure the refresh completes before reading values.

Retrieve a particular value with GetPivotData

For an example, suppose a worksheet named Summary contains a PivotTable named SalesPivot. The PivotTable has a value field captioned Sum of Sales and a row field named Region, with an item named West. The following macro asks for the West result by field and item rather than assuming that it is always in the same cell.

Sub ReadWestSales()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim result As Variant

    On Error GoTo ReadError

    ' Identify the worksheet and PivotTable by name.
    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("SalesPivot")

    ' Request the value field for the specified region.
    result = pt.GetPivotData("Sum of Sales", "Region", "West").Value2
    MsgBox CStr(result)
    Exit Sub

ReadError:
    MsgBox "Could not retrieve the PivotTable value: " & Err.Description
End Sub

Replace the worksheet, PivotTable, value-field, field, and item names with the names in your workbook. In Excel for Microsoft 365 on Windows, you can select the PivotTable and check its name in the PivotTable Analyze tab’s PivotTable Name box. Field and item text must match the report’s captions and labels. If the requested combination is unavailable, the error handler displays a message instead of leaving the failure unexplained.

GetPivotData is especially useful for a dashboard macro that needs a small number of defined figures. For example, the macro can retrieve a particular product’s total without relying on the product’s current row number. It is not a guarantee that a value will exist: filters, missing items, changed captions, or a different report structure can affect the request.

Read the displayed value area with DataBodyRange

If you need to process many displayed values, DataBodyRange gives you the PivotTable’s data area without requiring you to calculate each cell address. This example copies those values, in their current order, into column A of a separate worksheet named Extract. It copies values only; it does not add the row labels that explain what each number represents.

Sub CopyPivotValues()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim outputCell As Range
    Dim pivotCell As Range

    ' Point to the report and a separate destination sheet.
    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("SalesPivot")
    Set outputCell = ThisWorkbook.Worksheets("Extract").Range("A1")

    ' Stop if the PivotTable has no value area.
    If pt.DataBodyRange Is Nothing Then
        MsgBox "The PivotTable has no data body to copy."
        Exit Sub
    End If

    ' Copy each displayed value to the next output row.
    For Each pivotCell In pt.DataBodyRange.Cells
        outputCell.Value2 = pivotCell.Value2
        Set outputCell = outputCell.Offset(1, 0)
    Next pivotCell
End Sub

In this example, the result begins in Extract!A1 and proceeds down the column. If your report has multiple value fields, the data area can include several columns, so this simple loop places every cell into a single list and loses the original grid shape. To preserve rows and columns, assign the range’s values to a suitably sized destination range, or copy the range’s dimensions and contents deliberately. If labels are needed, include them through a broader range or retrieve them separately.

Choose the right range for the task

Excel exposes several useful PivotTable ranges, and they do not mean the same thing. DataBodyRange targets the cells containing summarized values. TableRange1 covers the PivotTable report but excludes report-filter fields; TableRange2 includes those page fields as well. These ranges can be more useful than selecting the entire worksheet or hard-coding a large address.

Before using a range, consider what your macro needs to include: headings, row labels, column labels, grand totals, and report filters may all matter. The exact layout can change with the report design. If your code depends on a particular cell within one of these ranges, validate that the expected label or heading is present before treating the cell as a specific measure.

For code that needs to locate one label in a changing report, another option is to search the PivotTable range for that label and then read an adjacent value. This can be practical for irregular reports, but it depends on the label being unique and the layout relationship remaining consistent. When the data-field and item combination is known, GetPivotData usually communicates the request more directly.

Refresh the report before reading when necessary

A macro can refresh a PivotTable before retrieving its values, but whether that is appropriate depends on the workbook. A PivotTable may use a worksheet range, an Excel table, an external connection, or a data model. Refreshing can take time, prompt for credentials, or fail if a connection is unavailable. A macro should not assume that a refresh succeeded merely because it requested one.

For a simple worksheet-based report, the PivotTable’s RefreshTable method refreshes that report. Excel also offers Data > Refresh All to refresh workbook connections and related reports. If your procedure depends on refreshed external data, account for the connection’s behavior and confirm the refresh has finished before reading the result. Refreshing a report can also change its layout if the underlying data introduces new items.

Where the source data is already current and the PivotTable is already refreshed, an additional refresh may be unnecessary. That can make a macro faster and avoid triggering a connection the user did not intend to access. Document the expected freshness of the report so that later users understand whether the macro reads the last saved PivotTable result or requests new source data.

Common problems and safeguards

  • Names do not match. A value field may be captioned Sales, Sum of Sales, or a custom name. Use the actual caption displayed in the PivotTable, and check field and item names for spelling and spacing.
  • The layout changed. Hard-coded worksheet addresses can point to another label or total after filtering or rearranging fields. Prefer PivotTable-aware references for defined results, or verify headings when using positional ranges.
  • The requested combination is absent. An item may not exist in the source data, or current filters may leave no result. Handle errors and avoid assuming every requested category is present.
  • The report is stale. A macro can correctly read a value that is no longer current because the PivotTable has not been refreshed. Refresh only when required and make failures visible.
  • The output lacks context. Copying only DataBodyRange gives you values, not necessarily the labels needed to interpret them. Include labels or write a defined output format.

Save a copy of the workbook before running a macro. VBA changes are not undone with Ctrl+Z, and a macro may overwrite cells or alter workbook content. The examples are for desktop Excel; Excel for the web does not run VBA macros. Excel for Mac supports VBA, although menus and some platform-dependent features can differ from Windows.

VBA versus worksheet formulas and direct source data

VBA is a good fit when a repeatable procedure needs to extract several PivotTable results, prepare a report, or move values into another sheet. It is not always necessary just to display a number in a worksheet. A formula that refers to a PivotTable result can update as the workbook recalculates, while a macro is useful when you need a sequence of actions or a saved output snapshot.

Another alternative is to calculate from the original source records. That may be preferable when the desired result is not represented in the PivotTable or when you need row-level records rather than summarized totals. However, reproducing the PivotTable’s filters, grouping, and aggregation rules can produce a different answer if those rules are not matched. Decide whether the report itself is the authoritative result or whether the source records are the appropriate input.

For a one-off copy, selecting and copying the PivotTable in Excel may be simpler than writing code. For a repeated task with changing data, VBA’s object model helps make the procedure repeatable, provided the macro checks names, missing items, refresh status, and the intended output location.

Frequently Asked Questions

Can VBA read a PivotTable cell by its address?
Yes. You can read a worksheet cell’s Value or Value2, but its meaning may change when the PivotTable layout changes. Use a stable PivotTable-aware request such as GetPivotData when you know the fields and items you want.

Does GetPivotData return the raw source rows?
No. It returns a value from the PivotTable report, typically an aggregation such as a sum or count. To retrieve individual source records, work with the source table or use another extraction approach.

Can a macro read filtered PivotTable results?
Yes, it can read values shown by the current report, but the result available for a requested field and item depends on the report’s current state. Test the request with the filters your workbook uses and handle cases where a combination is unavailable.

Can Excel for the web run this VBA?
No. Excel for the web can open and edit many macro-enabled workbooks, but it does not run VBA macros. Use desktop Excel to execute VBA.

Why does GetPivotData fail after I rename a field?
The method uses the PivotTable’s field and data-field captions. If a caption changes, update the corresponding text in the VBA request. Also check that the item still exists and that the specified PivotTable name is correct.

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.