Refreshing All Pivot Tables Simultaneously in Excel. Just as you can automate the creation of a pivot table, there are several ways to automatically refresh it as well. In this post, I’ll share three simple methods to refresh all the pivot tables in your workbook effortlessly.
Refreshing All Pivot Tables Simultaneously in Excel
Note: Proficiency in Pivot Tables is considered an intermediate Excel skill.
Using the “Refresh All” Button for Workbook-wide Updates
The “Refresh All” button offers a quick and straightforward way to update all the pivot tables in a workbook with just a single click.
Steps:
- Navigate to the Data Tab ➜ Connections ➜ Refresh All.
Auto-Refreshing Pivot Tables Upon Opening a Workbook
If you prefer to refresh all pivot tables automatically when opening a workbook, follow these setup steps.
Steps:
- Select any pivot table from your workbook.
- Right-click and choose “PivotTable Options.”
- Go to the Data Tab ➜ Check “Refresh Data When Opening A File.”
- Click OK.
Using VBA Code for One-Click Pivot Table Updates
VBA (Visual Basic for Applications) allows you to create a custom macro for refreshing all pivot tables with a single click. Utilize the provided code below:
Sub RefreshCustomPivotTable()
With ActiveSheet
.PivotTables.RefreshTable
End With
End Sub
You can assign this macro to a button, enabling you to update all pivot tables on the active worksheet seamlessly.
Refreshing Specific Pivot Tables with VBA
If you have specific pivot tables that require updating, you can customize the VBA code accordingly.
Sub RefreshCustomPivotTable()
With ActiveSheet
.PivotTables("PivotTable1").RefreshTable
.PivotTables("PivotTable2").RefreshTable
.PivotTables("PivotTable3").RefreshTable
.PivotTables("PivotTable4").RefreshTable
.PivotTables("PivotTable5").RefreshTable
End With
End Sub
Adjust the pivot table names to match those in your workbook. To automatically update these custom pivot tables each time you open the workbook, rename the macro to “auto_open.”
Sub auto_open()
With ActiveSheet
.PivotTables("PivotTable1").RefreshTable
.PivotTables("PivotTable2").RefreshTable
.PivotTables("PivotTable3").RefreshTable
.PivotTables("PivotTable4").RefreshTable
.PivotTables("PivotTable5").RefreshTable
End With
End Sub
