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
