Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Updating Multiple PivotTables at Once.
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.
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: 
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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Wonder what happened to the data behind a PivotTable? It could be in a number of places, and tracking it down could be a ...
Discover MoreConditional formatting is very powerful, and you can use it to dynamically adjust how your data looks. Excel allows you ...
Discover MorePivotTables are used to boil down huge data sets into something you can more easily understand. They are very good simple ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-01-14 09:30:58
Jonathan
Allen,
I tend to just use the Refresh All button in the Data Tab (but have now placed it onto the Access Toolbar thanks to a comment above).
Is there any way of making this macro applicable to all open workbooks?
Thanks,
Jonathan
2016-09-30 03:27:21
Michael (Micky) Avidan
@To whom it may concern,
To my opinion - pressing: Ctrl+Alt+F5 is the most quickest way to Refresh *ALL* PivotTables in the active workbook.
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL
2016-09-28 10:33:26
Tony
Hi and thank you for the code. It is a great benefit just to see and learn from it. Does Refresh All apply to the entire workbook or just all of the pivot tables on the active worksheet? Thank you.
2016-08-07 21:07:39
Tim
Further to the comment above I put the "Refresh All" icon in the 'Quick Access Toolbar' so it is always visible.
I would only use a macro if I wasn't to selectively refresh PivotTables because one or other was slow to refresh and not needed.
2013-12-09 02:41:14
Jakob
Use Refresh all in data tab.
Much easier.
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 © 2020 Sharon Parq Associates, Inc.
Comments