Written by Allen Wyatt (last updated September 19, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003
For certain types of data analysis, PivotTables can be very handy. If you have a workbook that contains several PivotTables, all based on the same data, you may wonder if there is a way to update them all at once, rather than going through them individually and updating them.
There is no Excel command that allows you to update all PivotTables, but you can create a short macro that will do the job for you. The following macro, RefreshAllPivots, steps through each worksheet in a workbook, checks to see if there are any PivotTables, and then updates them if there are.
Sub RefreshAllPivots() Dim wks As Worksheet Dim pt As PivotTable For Each wks In Worksheets For Each pt In wks.PivotTables pt.RefreshTable Next pt Next wks End Sub
If you do a lot of work with multiple PivotTables, you may want to assign the macro to a shortcut key, a toolbar button, or to a menu option so that you can run it easier. (Information on how to assign macros to toolbars, shortcut keys, and menus is covered in other issues of ExcelTips.)
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2287) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Program Successfully in Excel! This guide will provide you with all the information you need to automate any task in Excel and save time and effort. Learn how to extend Excel's functionality with VBA to create solutions not possible with the standard features. Includes latest information for Excel 2024 and Microsoft 365. Check out Mastering Excel VBA Programming today!
If you are using a data set that includes a number of zero values, you may not want those values to appear in a ...
Discover MoreWhen you create a PivotTable based on data that contains lots of text, you may be surprised to find that your text is ...
Discover MoreCreate a complex PivotTable and you may find that your system slows to a crawl. The reason for this may be due to the way ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments