Refresh All Pivot Caches with Excel Macro

Instead of manually updating all the pivot tables in your workbook, use a macro to do the job quickly. This macro tries to refresh all the pivot caches in the active workbook. If the macro can’t refresh a pivot cache, it will show you a message with the pivot cache index number, and the error description.

Pivot Caches

When you create a pivot table in Excel, a pivot cache is automatically created in the background. That’s a special memory area where the pivot table records are saved.

When you want to update a pivot table, you can use the Refresh command on the pivot table, or you can use a macro to refresh its pivot cache.

NOTE: With either method, all pivot tables that use the same pivot cache will also be refreshed.

Pivot Cache Refresh Macro

The macro code shown below tries to update all of the pivot caches in the active workbook. Sometimes an update won’t work, perhaps because a pivot table sheet is protected.

At the end of the macro, a message appears automatically. The message shows the macro results:

count of pivot caches in the active workbook
count of failed pivot cache refreshes

User Defined Functions

In the Pivot Cache Macros sample file that has this macro, two of the worksheets have formulas with User Defined Functions UDF), such as the GetMemory function in the screen shot below. The formulas are in cells at the top of each sheet, above the pivot tables.

Like many User Defined Functions, those UDFs will run automatically, when other macros make changes to the worksheets. That can cause errors in the other macros.

To prevent any problems with the UDFs, the Refresh All Pivot Caches macro temporarily turns off calculation for those 2 worksheets. At the end of the macro, calculation is turned back on again.

Pivot Cache Refresh Macro Code

Here is the pivot cache refresh macro code. Copy this code to a regular code module in your workbook, or store it in a workbook that’s always open, such as your Personal Macro workbook.

Sub RefreshAllPivotCaches()
' Developed by Contextures Inc.
' www.contextures.com
Dim wb As Workbook
Dim lPCs As Long
Dim lPC As Long
Dim lProb As Long
Application.EnableEvents = False
On Error Resume Next

'turn off calc on sheets with UDF formulas
Sheets("PivotRep").EnableCalculation = False
Sheets("PivotProd").EnableCalculation = False

Set wb = ActiveWorkbook
lPCs = wb.PivotCaches.Count

For lPC = 1 To lPCs
  wb.PivotCaches(lPC).Refresh
  If Err.Number <> 0 Then
    MsgBox "Could not refresh pivot cache " & lPC _
      & vbCrLf _
      & "Error: " _
      & vbCrLf _
      & Err.Description
    Err.Clear
    lProb = lProb + 1
  End If
Next lPC

MsgBox "Refresh is complete. " _
  & vbCrLf _
  & "Pivot Cache Count: " & lPCs _
  & vbCrLf _
  & "Failed refreshes: " & lProb

'turn on calc on sheets with UDF formulas
Sheets("PivotRep").EnableCalculation = True
Sheets("PivotProd").EnableCalculation = True

Application.EnableEvents = True

End Sub

Get the Workbook

To get the sample Excel workbook with the Refresh All Pivot Caches macro, and other pivot cache macros and User Defined Functions, go to the Pivot Cache Macro page on my Contextures website.

The zipped Excel file is in xlsm format, so be sure to enable macros, if you want to test any of the sample macros.

__________________