Excel PivotTables do not refresh automatically when the data source is updated.
Now, you can refresh all of the PivotTables in your workbook with just 2 or 3 clicks. But this is not something you need to do when you work with formulas, charts or Conditional Formatting. So you might forget, or just find it irritating to do it regularly.
This tutorial walks you through the VBA code to refresh the PivotTable automatically.
New to VBA and want to learn quickly? Sign up to our online Excel VBA course.
Watch the Video
Automatically Refresh the PivotTable When Data is Changed
The first thing you need to do, if not done already, is to save your workbook as a Macro Enabled Workbook. This allows us to save macros to it.
Click File > Save As and select Macro Enabled Workbook from the Save as Type list.
Open up the Visual Basic Editor by pressing Developer > Visual Basic, or press Alt + F11.
Double click on the Sheet that contains the PivotTable source data from the Project Explorer window (shown below).
Select Worksheet from the Object drop down list, and then the Change event from the Procedure drop down list. The Worksheet Change sub will appear like the image below.
The code below refreshes the Pivot Cache of a PivotTable named Sales on a worksheet named PivotTable. This is done automatically when data on the sheet containing the code is changed.
Type or copy the code into your Worksheet Change sub and adapt it to your needs.
Private Sub Worksheet_Change(ByVal Target As Range Worksheets("PivotTable").PivotTables("Sales").PivotCache.Refresh End Sub
Refresh all the PivotTables on a Worksheet
You may have multiple PivotTables on a worksheet to update. Also you may not know the names of the PivotTables, so you cannot reference them as I did in the previous example.
The code below uses a variable named pt. This is used to loop through each PivotTable on the PivotTable worksheet and refresh each one.
Private Sub Worksheet_Change(ByVal Target As Range) Dim pt As PivotTable For Each pt In Worksheets("PivotTable").PivotTables pt.PivotCache.Refresh Next End Sub
Refresh all the PivotTables in the Workbook
What if your PivotTables are on multiple worksheets, and you do not necessarily know the names of the worksheets or PivotTables.
Well the code below will loop through all of the sheets of the workbook and refresh all of the PivotTables.
Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim pt As PivotTable For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables pt.PivotCache.Refresh Next Next End Sub
More Excel VBA Tutorials
- Excel macro to email a workbook as an attachment
- Export all worksheets as a PDF
- Format specific words in a cell – Excel VBA