[VBA] How to Retrieve Specific Pivot Table Values (GetPivotData)

Overview

In Excel Pivot Tables, you often encounter situations where you want to automatically retrieve only the aggregated results that match specific conditions. The GetPivotData function is very useful for this purpose.

In this article, I will introduce how to use VBA to retrieve specific aggregated values from a Pivot Table. This allows you to accurately get values such as sales figures or quantities, which is helpful for automating reports and analysis tasks.

Sample Code

Sub GetSpecificPivotValue()

    Dim result As Variant

    ' Retrieve specific data from the first PivotTable
    result = ActiveSheet.PivotTables(1).GetPivotData( _
        DataField:="Total Amount", _
        Field1:="Branch Name", _
        Item1:="Sapporo Branch" _
    )

    MsgBox "The aggregated result is " & result & " yen."

End Sub

Code Explanation

• PivotTables(1) This refers to the first Pivot Table on the active sheet.
• .GetPivotData(...) By using this method, you can retrieve only the aggregated value from the Pivot Table that matches the specified conditions.
• DataField:="Total Amount" Specifies the numeric field (aggregation target) you want to retrieve. In this example, it is set to “Total Amount”.
• Field1:="Branch Name" / Item1:="Sapporo Branch" Specifies the field name and item name to use as a filter condition. For instance, this code specifies fetching the “Total Amount” for the row where the “Branch Name” is “Sapporo Branch”.
• MsgBox Displays the retrieved value in a message box. This is useful for verification during processing or debugging.

Important Notes

1. Visibility: If the corresponding data is not currently displayed (visible) in the Pivot Table, an error will occur.
2. Accuracy: Be careful with spelling errors in field names and item names. It is important to match the text exactly.
3. State Dependency: GetPivotData relies on the current state of the Pivot Table and does not force a data refresh/recalculation on its own.

Use Cases

• Batch retrieval of sales data by salesperson or product using a macro.
• Automatic generation of monthly reports.
• Extracting only the necessary aggregated data to match a checklist.

Summary

In this article, I introduced how to use the VBA GetPivotData function to retrieve aggregated values matching specific conditions within a Pivot Table. By combining conditions, you can perform flexible data extraction.

If you are looking to automate Pivot Table operations, please try incorporating this method into your workflow.