Excel PivotTables do not refresh automatically when the data source is updated. Learn how to automatically refresh PivotTables with Excel VBA.
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
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets(“Sheet4”).PivotTable(“DATA”).PivotCache.Refresh
End Sub
When using the code I just get the Run Error 438 and it states like “Object doesn’t support this property or method”. How can I fix this issue?
Thank you
Hi Vaithiyanathan, thank you for your comment.
It needs to be PivotTables. You are missing an s.
It does not update my pivot table.
I tried code on refreshing the pivottable tha is on the same page and it works.I inserted the code to Sheet1 where my data is and it does not refresh the Pivot table on the same page. I am using Excel 2013 and planning to insert multiple pivot tables on multiple sheets.
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
I tried code on refreshing the pivottable tha is on the same page and it works.I inserted the code to Sheet1 where my data is and it does not refresh the Pivot table on the same page. It highlights “pt.PivotCache.Refresh” yellow. I am using Excel 2013 and planning to insert multiple pivot tables on multiple sheets.
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
I tried code on refreshing the pivottable tha is on the same page and it works.I inserted the code to Sheet1 where my data is and it does not refresh the Pivot table on the same page. It says Run time error 1004 – application defined or object defined error. I am using Excel 2013 and planning to insert multiple pivot tables on multiple sheets.
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