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: 

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

Exercise Workbook:

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

DOWNLOAD EXCEL WORKBOOK


This is our data:

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

These are the two pivot tables using this data source:

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

Now let’s make change to one big sales value! Let us see if the pivot tables will reflect these values.

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

STEP 1: Go to Developer > Code > Visual Basic

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

 

STEP 2: Paste in your code and Select Save. Close the window afterwards.

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

 

STEP 3: Let us test it out!

Open the sheet containing the data. Go to Developer > Code > Macros

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

 

Make sure your macro is selected. Click Run.

Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

 

With just one click, all of your pivot tables are now refreshed!

 Refresh All Pivot Tables Using Macros In Excel | MyExcelOnline

 

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