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.

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 .CacheIndex property.

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 Pivot Sheet1 and the name of the table is AQI for CBSAs 2019. Our single line of code would be:

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.

I'll take a free VBA Developer Kit

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.

Using 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:

Dedicated Code Module for Worksheet with Highlighting
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 Worksheet from General, and finally, choose Change from the events dropdown. This will fill in some code that will trigger when changes are detected on that worksheet.

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.