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. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Updating Multiple PivotTables at Once.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
If you modify the data on which a PivotTable is based, you'll need to refresh the table so it reflects the modified data. ...
Discover MoreYou can format PivotTables using either manual formatting or automatic formatting. You need to be careful, however, as ...
Discover MoreIf you ever try to edit a PivotTable and get an error that tells you that the "underlying data was not included," it can ...
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 © 2022 Sharon Parq Associates, Inc.
Comments