How to Refresh All Pivot Tables in VBA

We will introduce how to refresh all pivot tables in VBA with examples.

Refresh All Pivot Table in VBA

First, we will understand what a pivot table is and why it is used in Excel. The pivot table is an interactive table used to summarize a large amount of data.

It is normally used to display the data such as monthly sales reports, monthly expense details, or something similar.

Once we have updated the data in our Excel sheet or added new data to our sheet, sometimes pivot tables need to be refreshed to display the correct data.

VBA provides two different methods that can be used to refresh pivot tables. One method is used to refresh everything in a seamless batch.

First, we will discuss how to create a pivot table in Excel. To create a new pivot table, we need to go to Insert and click on Tables, then PivotTable.

A window will pop up that will allow us to select the range based on which the pivot table will be created, as shown below.

The RefreshAll method is used to refresh all the pivot tables in the workbook. Let’s go through an example and use this method to refresh all the pivot tables in a workbook, as shown below.

Sub refresh()
ThisWorkbook.RefreshAll
End Sub

This will refresh all the pivot tables from the workbook. If we want to refresh a certain pivot table from a certain sheet, we can use the PivotTables method of a sheet and apply the Refreshtable method to refresh that certain pivot table, as shown below.

Sub refresh()
Sheet1.PivotTables("myPivotTable").Refreshtable
End Sub

Output: