Macro 63: Refresh All PivotTables Workbook
It’s not uncommon to have multiple PivotTables in the same workbook. Many times, these PivotTables
link to data that changes, requiring a refresh of the PivotTables. If you find that you need to refresh your
PivotTables en masse, you can use this macro to refresh all PivotTables on demand.
How it works
It’s important to know that each PivotTable object is a child of the worksheet it sits in. The macro
has to first loop through the worksheets in a workbook first, and then loop through the PivotTables in
each worksheet. This macro does just that — loops through the worksheets, and then loops through
the PivotTables. On each loop, the macro refreshes the PivotTable.
Sub Macro63()
‘Step 1: Declare your Variables
Dim ws As Worksheet
Dim pt As PivotTable
‘Step 2: Loop through each sheet in workbook
For Each ws In ThisWorkbook.Worksheets
‘Step 3: Loop through each PivotTable
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub
1. Step 1 first declares an object called ws. This creates a memory container for each worksheet
we loop through. It also declares an object called pt, which holds each PivotTable the macro
loops through.
2. Step 2 starts the looping, telling Excel we want to evaluate all worksheets in this workbook.
Notice we are using ThisWorkbook instead of ActiveWorkbook. The ThisWorkbook
object refers to the workbook that the code is contained in. The ActiveWorkbook object
refers to the workbook that is currently active. They often return the same object, but if the
workbook running the code is not the active workbook, they return different objects. In
this case, we don’t want to risk refreshing PivotTables in other workbooks, so we use
ThisWorkbook.
3. Step 3 loops through all the PivotTables in each worksheet, and then triggers the
RefreshTable method. After all PivotTables have been refreshed, the macro moves
to the next sheet. After all sheets have been evaluated, the macro ends.
ThisWorkbook.RefreshAll. This refreshes all the PivotTables in the workbook. However,
it also refreshes all query tables. So if you have data tables that are connected to an external
source or the web, these will be affected by the RefreshAll method. If this is not a concern,
you can simply enter ThisWorkbook.RefreshAll into a standard module.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
