How to Reference a Pivot Table in VBA

Pivot tables are powerful tools in Excel that summarize data, enabling quick analysis and reporting. Automating the interaction with pivot tables using VBA can greatly enhance your productivity. This tutorial will guide you through the process of referencing and manipulating a pivot table in VBA, ensuring you can automate your Excel tasks effectively.

Step 1: Understand the Workbook and Worksheet Object

Before you can work with pivot tables in VBA, it’s essential to understand how to reference the workbook and worksheet containing your pivot table. You will typically use the Workbook and Worksheet objects to accomplish this.

Step 2: Reference a Pivot Table by Name

To reference a pivot table, you first need to know its name. You can find the pivot table’s name in the PivotTable Fields pane in Excel. Once you have the name, you can reference the pivot table with the following code:

Ensure that “YourPivotTableName” matches the name of the pivot table you want to work with.

Step 3: Referencing Pivot Fields, Items, and Data

With the pivot table referenced, you can interact with its various elements, such as fields and items. For example, you may want to reference a specific field or change a field’s position in the layout:

Similarly, to work with individual items within a field:

Step 4: Accessing Pivot Table Data

To reference a specific cell within a pivot table, which could be valuable for dynamic reporting or dashboards, you use the PivotTable.GetPivotData method:

This will display the value from the specified coordinates within the pivot table.

Step 5: Updating the Pivot Table

After changing the pivot table, such as filtering a field or adding a new data field, you will often need to refresh the pivot table to update its display with the new configuration:

This code refreshes the data of the specified pivot table so that it matches any new or modified source data.

Note: It’s a best practice to turn off screen updating when you manipulate pivot tables using VBA to speed up your code.

Full Code:


Pivot table:

Table Fields:


Referencing a pivot table in VBA is an essential skill for automating your Excel workflows. By following the steps outlined in this tutorial, you can easily interact with and manipulate pivot tables, making your reports and analyses more dynamic and responsive. Remember to reference the pivot table and its components accurately, and refresh the table after making changes to see up-to-date results.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba