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:
Step 1
From your keyboard, press Alt+F11.
Step 2
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.
Step 3
Paste either or both of the macros into your new module.
Step 4
Exit the VBE and return to your worksheet by pressing Alt+Q.
Step 5
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