Tom’s Tutorials For Excel: Refreshing All Your PivotTables at Once
Here are two macros you can use for refreshing all the pivot tables on your active worksheet, or all the pivot tables in the entire worbook.
Sub RefreshAllActiveSheet() Dim Pt As PivotTable For Each Pt In ActiveSheet.PivotTables Pt.RefreshTable Next Pt End Sub Sub RefreshAllActiveWB() Dim Sh As Worksheet, Pt As PivotTable For Each Sh In Sheets For Each Pt In Sh.PivotTables Pt.RefreshTable Next Pt Next Sh End Sub
If you are new to VBA and not sure how to install macros, follow these simple steps:
From your keyboard, press Alt+F11.
Step 1 took you to the Visual Basic Editor (VBE), where programming code is maintained.
From the menu bar at the top of the VBE, click
Insert > Module.
Paste either or both of the macros into your new module.
Exit the VBE and return to your worksheet by pressing Alt+Q.
There are many ways to run a macro. An easy method is to press Alt+F8 to call the Macro dialog box, select your macro's name in the window, and click the Run button, as indicated by the following pictures.
Leave a Reply