Pivot Tables are powerful tools, but they’re only as strong as the data in them. If the data is outdated, then they are no longer useful for understanding the current state of things and they’re certainly not useful for forward projections. Updating your Pivot Table data when you open your Excel file is very simple through the Excel GUI, but you can easily overlook the need to update regularly. In that case, automated updates might be a better idea. We’ve published a tutorial on scheduling macros before, so once you know how to update Pivot Tables programmatically using VBA, you can always automate the entire process, too.
- Tables and Caches
- Refreshing Pivot Table
- Refreshing a PivotCache
- Refreshing on Changes
Pivot Tables and Pivot Caches
There are three main components to a Pivot Table: the original data, the Pivot Cache, and the table itself. A
PivotCache is an object that lives at the workbook level, so it can be accessed by any Pivot Table on any worksheet. A
PivotTable is a sheet-level object, as it must exist on a particular sheet (otherwise you wouldn’t be able to see it!).
Pivot Caches prepare the original data to be used in a Pivot Table, and one cache can be used for many different tables. Importantly for this tutorial, if two Pivot Tables share the same cache, any refresh of one table will first update the underlying cache and then update all tables associated with that cache. This means updates can very efficiently flow through a workbook, but that is not always desirable. In order to update tables individually via VBA, it is necessary that the tables to be refreshed independently rely on separate caches. You can see which cache any table relies on through its
Refreshing Pivot Table
The easiest way to refresh a table is simply calling the
.RefreshTable method on it. If you know the name and location of the table, it is a simple one-liner. From our previous VBA pivot table tutorial, let’s say the table we want to refresh lives on
Sheets("Pivot Sheet1").PivotTables("AQI for CBSAs 2019").RefreshTable
Make powerful macros with our free VBA Developer Kit Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.
Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.
When Pivot Tables Share a Cache
Importantly, if any other tables share the same Pivot Cache - that is, their
.CacheIndex values are equal - then all associated tables will also update. This is a very efficient implementation because you only need to know the name of one table, and you only need to write one line of code. The underlying programming logic and processing is already optimized by Microsoft and the VBA language developers for us.
So, if a workbook is full of Pivot Tables all built off a single cache, then this approach will actually update the entire workbook without any other coding required.
When Tables Do NOT Share a Cache
Sometimes it is important to separate the underlying caches in order to accomplish your needs. One drawback of using one cache for all tables is it becomes impossible to separate tables on certain dimensions, such as groupings. In such cases, it is better to separate caches. If tables do not share a cache, then they can be refreshed in the same way as above, but it will need to be done over the entire collection. This can be accomplished via nested loops, which is shown in the next section.
Getting Names and Iterating
If you know the name of the sheet but not the name of the table, you can always check the
.Name property of all the Pivot Tables on that page, which will live in the
PivotTables collection. To clarify,
PivotTable refers to a single table, while the plural
PivotTables actually refers to the collection of tables.
debug.print and the Immediate window (keyboard shortcut is Ctrl+g), you can check all the names:
Sub check_table_names() For Each tbl In Sheets("Pivot Sheet1").PivotTables Debug.Print tbl.Name Next tbl End Sub
You don’t have to stop here, though. You could nest for-loops and do this for every worksheet and every table (tables on different sheets can have the same name, though!):
Sub check_table_names_all_sheets() For Each sht In ThisWorkbook.Sheets For Each tbl In sht.PivotTables Debug.Print tbl.Name, sht.Name 'to print table and the sheet where it resides Next tbl Next sht End Sub
and now you have the name of every table and the sheets on which they reside, so you can easily target specific Pivot Tables to be updated.
If you have separate Pivot Caches for each table, you can use this nested for-loop method to update every table. Simply replace the
debug.print line above with
tbl.RefreshTable, like this:
Sub refresh_pivot_tables_all_sheets() For Each sht In ThisWorkbook.Sheets For Each tbl In sht.PivotTables tbl.RefreshTable Next tbl Next sht End Sub
Using this method, each table in the entire workbook can be updated independently, regardless of its dependence on the same Pivot Cache.
There’s another way to refresh all the pivot tables in a workbook. Instead of iterating through each worksheet and pivot table, you can simply call the
.RefreshAll method of the workbook. Here’s how your simplified macro might look if you wanted to refresh all the pivot tables in your workbook:
Sub refresh_all_pivot_tables() ThisWorkbook.RefreshAll End Sub
Refreshing a PivotCache
Earlier we talked about the relationship between Pivot Tables and Pivot Caches. Instead of refreshing a pivot table, you could choose to refresh the pivot cache directly and all pivot tables linked to this cache will automatically be updated. Let’s show you how that looks.
Refreshing a single PivotCache
What if you wanted to update a specific
PivotCache rather than a specific pivot table? The code is similar to the first code snippet we presented in this tutorial, but you have to drill down one more level to get to the
PivotCache. Take a look:
Sheets("Pivot Sheet1").PivotTables("AQI for CBSAs 2019").PivotCache.Refresh
Notice how this version of the code uses the
.Refresh method instead of the
.RefreshTable method. That’s because this method refreshes the entire cached data, whereas the
.RefreshTable method refreshes the PivotTable from the source data. It’s a subtle difference, I’ll admit.
Refreshing all PivotCaches
Instead of looping through and refreshing all the pivot tables, you could choose to loop through and refresh all the underlying Pivot Caches. Here’s how you would to that:
Sub Refresh_All_Pivot_Table_Caches() Dim PCache As PivotCache For Each PCache In ThisWorkbook.PivotCaches PCache.Refresh Next PCache End Sub
The downstream pivot tables are automatically updated when the underlying pivot cache is refreshed, so this macro essentially does the same thing as the
refresh_all_pivot_tables macro we presented earlier.
Refreshing on Changes
Another nifty feature of Excel is its worksheet events. Worksheet events are similar to workbook events, but they’re triggered by events on a specific sheet. These events can be used to update Pivot Tables whenever the original data is changed (assuming the data lives in the same workbook as the Pivot Tables).
The original data is often updated by hand to fix errors. In those cases, the original datasheet can be monitored for changes with the
Worksheet_Change event. Whenever a change occurs to the original data worksheet, a macro can be triggered to update the Pivot Tables.
In order to access worksheet events, you need to open the worksheet’s dedicated code module:
The Dedicated Worksheet Code Module
Double click the red box to get the dedicated code for that worksheet. For our purposes, use the worksheet that contains the editable original data. Then, change the orange box to
Now, you can add your Pivot Table refresh code in this private subroutine, and any time a change is committed (someone presses enter for new data), the code here will be triggered.
This is particularly useful to ensure tables are always updated, since any change to the original data will flow over to the tables immediately. This frees users from updating the tables manually; they don’t even have to remember to do so, since it will automatically work in the background.
A caveat: this method is only practical for singular changes! If many changes are being made or the pivot table is on the same sheet as the data, Excel will become unusable as it continually updates tables for every single commit. If several changes are to be made to the original data at once, consider another method, such as a button users can press once they finish making their changes. Alternately, you can disable events in your
worksheet_change code by toggling Application.EnableEvents, like this:
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ThisWorkbook.RefreshAll Application.EnableEvents = True End Sub
Updating the information in a Pivot Table is essential for accurate data analysis, and in Excel, it is very easy to do programmatically. While it can be done through the GUI, it is often better to automate the process using a worksheet event. If you’d rather the data only be updated once the user is ready, you could have a button the user presses after changes have been made to the source data.
I hope you enjoyed this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.