VBA – Refresh Pivot Table when Data Changes

This tutorial will demonstrate how to Refresh a Pivot Table when the data changes using VBA.

Pivot tables are an exceptionally powerful data tool of Excel.   Pivot tables enable us to analyze and interpret large amounts of data by grouping and summarizing fields and rows.

When we create a Pivot table, the data is stored in what is known as a Pivot table cache.   The data is therefore stored in the cache memory of the PC which speeds up the Pivot table.

If we update the data that the Pivot table relies on, the Pivot table itself will not automatically get updated.  We would need to click on the Refresh All button in the Data tab on the Ribbon to refresh the Pivot table.

Refreshing the Pivot Table in VBA

When the data changes, we can write a macro in the Worksheet_Change Event to update the Pivot table.  In order to access the worksheet Change Event, we need to go to the Visual Basic Editor in Excel (VBE).

Press Alt+F11 to access the VBE or in the Ribbon, select Developer > Visual Basic.

Note: If you don’t see the Developer Ribbon, you’ll need to enable it.

In the Project Explorer, select the Sheet that contains the Pivot table.

 

In the Object drop-down box, select Worksheet.   This will automatically add an event for Selection_Change.

 

In the Procedure drop-down box, select Change.

 

A Change Event will be added to the VBA module contained within the Sheet.

 

NOTE: We can delete the Worksheet_SelectionChange event as it is not required.

Within the Worksheet_Change event, type the following code:

ActiveSheet.PivotTables("PivotTable1").RefreshTable

Switch back to the Excel sheet and amend a value in the data table.  The data in the Pivot table will then be automatically updated.