Return to VBA Code Examples

VBA – Refresh Pivot Table when Data Changes

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

VBA Pivot ChangeEvent Intro

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.

VBA Pivot Change Event DeveloperRibbon

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.

VBA Pivot Change Event VBE

 

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

VBA Pivot Change Event Select Object

 

In the Procedure drop-down box, select Change.

VBA Pivot Change Event Select Change

 

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

 

VBA Pivot Change Event

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

Within the Worksheet_Change event, type the following code:

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.

VBA Pivot Change Event Updated

 

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!