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:

DOWNLOAD EXCEL 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

 

HELPFUL RESOURCE:

101 Macros Book

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn