I use pivot tables a lot. They drive multiple reports in my workbooks and it is not unusual for me to have 6 or so pivots in each spreadsheet. When the underlying data changes I spend quite a lot of time clicking through each pivot table to refresh it.

Refresh All Button to refresh all pivots at once

I was happy to discover the Refresh All button in excel 2007 today. Press it and all your pivots will refresh as if by magic!

You’ll find it on the Data ribbon.

Refreshing All Pivots using Macro Code

I also have a number of macro enabled workbooks that generate reports and update pivot tables. I have been writing code that specifies the pivot table by name and then refreshes it but it can be tricky to find the pivot table name. You can use refresh all within the code to refresh all pivot tables at once.

Try:

ActiveWorkbook.RefreshAll

or

ThisWorkbook.RefreshAll

Originally published on my excelpivots blog.