It is so frequent that whenever I update my data, I forgot to refresh my Pivot Tables. We can now refresh all pivot tables using Excel Macros!
Make sure your Excel has the Developer Tab enabled following this tutorial.
I explain how you can do this below step by step!
What does it do?
Refresh all Pivot Tables
Copy Source Code:
Sub RefreshAllPivotTables() Dim pCache As PivotCache 'With just one loop, refresh all pivot tables! For Each pCache In ActiveWorkbook.PivotCaches pCache.Refresh Next pCache End Sub
Final Result:Â
Exercise Workbook:
This is our data:
These are the two pivot tables using this data source:
Now let’s make change to one big sales value! Let us see if the pivot tables will reflect these values.
STEP 1: Go to Developer > Code > Visual Basic
STEP 2: Paste in your code and Select Save. Close the window afterwards.
STEP 3:Â Let us test it out!
Open the sheet containing the data. Go to Developer > Code > Macros
Make sure your macro is selected. Click Run.
With just one click, all of your pivot tables are now refreshed!
Â
How to Refresh All Pivot Tables Using Macros In Excel