VBA Guide to Pivot Tables

Associated Files Download Links

This tutorial will show you how to work with Pivot Tables using VBA. Pivot Tables are data summarization tools that you can use to draw key insights and summaries from your data. We have a source data set in cells A1:D21 containing the details of products sold, shown below:

Pivot Table Source Data

Using GetPivotData to Obtain a Value

Assume you have a PivotTable called PivotTable1 with Sales in the Values/Data Field, Product as the Rows field and Region as the Columns field. You can use the PivotTable.GetPivotData method to return values from Pivot Tables.

The following code will return $1,130.00 (the total sales for the East Region) from the PivotTable:

In this case, Sales is the “DataField”, “Field1” is the Region and “Item1” is East.

The following code will return $980 (the total sales for Product ABC in the North Region) from the Pivot Table:

In this case, Sales is the “DataField”, “Field1” is Product, “Item1” is ABC, “Field2” is Region and  “Item2” is North.

You can also include more than 2 fields.

The syntax for GetPivotData is:

GetPivotData (DataFieldField1Item1Field2Item2…) where:

Parameter Description
Datafield Data field such as sales, quantity etc. that contains numbers.
Field 1 Name of a column or row field in the table.
Item 1 Name of an item in Field 1 (Optional).
Field 2 Name of a column or row field in the table (Optional).
Item 2 Name of an item in Field 2 (Optional).

 

Creating a Pivot Table on a Sheet

In order to create a Pivot Table based on the data range above, on cell J2 on Sheet1 of the Active workbook, we would use the following code:

The result is:
Creating a Pivot Table in VBA

Creating a Pivot Table on a New Sheet

In order to create a Pivot Table based on the data range above, on a new sheet, of the active workbook, we would use the following code:

Adding Fields to the Pivot Table

You can add fields to the newly created Pivot Table called PivotTable1 based on the data range above. Note: The sheet containing your Pivot Table, needs to be the Active Sheet.

To add Product to the Rows Field, you would use the following code:

To add Region to the Columns Field, you would use the following code:

To add Sales to the Values Section with the currency number format, you would use the following code:

The result is:

Adding Row, Column and Values Fields in VBA

Changing the Report Layout of the Pivot Table

You can change the Report Layout of your Pivot Table. The following code will change the Report Layout of your Pivot Table to Tabular Form:

Deleting a Pivot Table

You can delete a Pivot Table using VBA. The following code will delete the Pivot Table called PivotTable1 on the Active Sheet:

Format all the Pivot Tables in a Workbook

You can format all the Pivot Tables in a Workbook using VBA. The following code uses a loop structure in order to loop through all the sheets of a workbook, and delete all the Pivot Tables in the workbook:

To learn more about how to use Loops in VBA click here.

Removing Fields of a Pivot Table

You can remove fields in a Pivot Table using VBA. The following code will remove the Product field in the Rows section from a Pivot Table named PivotTable1 in the Active Sheet:

Creating a Filter

A Pivot Table called PivotTable1 has been created with Product in the Rows section, and Sales in the Values Section. You can also create a Filter for your Pivot Table using VBA. The following code will create a filter based on Region in the Filters section:

To filter your Pivot Table based on a Single Report Item in this case the East region, you would use the following code:

Let’s say you wanted to filter your Pivot Table based on multiple regions, in this case East and North, you would use the following code:

Refreshing Your Pivot Table

You can refresh your Pivot Table in VBA. You would use the following code in order to refresh a specific table called PivotTable1 in VBA:

You may also like some of this related content...

Ads