Macro to Create a Pivot Cache List in Excel Workbook

In a large Excel file, with several pivot tables, it’s easy to lose track of how many pivot caches there are. Instead of trying to manually figure things out, use this macro to create a pivot cache list.

How Many Pivot Caches?

How many pivot caches are there in your workbook?

• If there is one pivot table in the workbook, there will be one pivot cache
• If there are two or more pivot tables, there could be multiple pivot caches
• If there are two or more pivot tables that are based on different data sources, there will be at least one pivot cache for each of those data sources

Which Pivot Cache?

In a workbook with multiple pivot tables, you might not be able to tell which pivot cache a pivot table uses, just by checking its data source.

For example, even if two pivot tables have the same source data, they might use different pivot caches.

To see the details on all the pivot caches in a workbook, you can use a macro. Either create your own macro, or use my example, below.

Macro to Create a Pivot Cache List

Run this macro to list all the pivot caches in the active workbook. It adds a new sheet to the workbook, with a list of pivot caches, with the following details:

1. Cache Index Number
2. Number of Pivot Tables using the cache
3. Number of records in the cache
4. Source Type (xlDatabase or Other Source)
5. Data Source (table name or sheet name and address
6. Latest Refresh date and time
7. Is the pivot cache set to refresh on open?

NOTE: This feature (with a few more pivot cache details) is also in my Pivot Power Premium add-in.

The Pivot Cache List Macro Code

Add the following code to a regular worksheet module, then run the macro to create the list.

Sub ListAllPivotCaches()
' Developed by www.contextures.com
' list all pivot caches in active workbook
Dim pc As PivotCache
Dim wb As Workbook
Dim ws As Worksheet
Dim lRow As Long
Dim wsAll As Worksheet
Dim lPC As Long
Dim lPCs As Long
Dim lFields As Long
Dim lColDate As Long
Dim ptAll As PivotTable
Dim strSource As String
Dim strST As String
Dim rngS As Range
Dim strSourceR1C1 As String
On Error Resume Next
Application.EnableEvents = False

lRow = 1
lFields = 7
lColDate = 6
Set wb = ActiveWorkbook
lPCs = wb.PivotCaches.Count

If lPCs = 0 Then
  MsgBox "No pivot caches in the workbook"
  Exit Sub
End If
  
Set ws = Worksheets.Add
With ws
  .Range(.Cells(1, 1), .Cells(1, lFields)) _
      .Value = Array("Cache Index", _
        "PTs", _
        "Records", _
        "Source Type", _
        "Data Source", _
        "Refresh DateTime", _
        "Refresh Open")
End With

lRow = lRow + 1

For Each pc In wb.PivotCaches
  'count the pivot tables
  lPC = 0
  Select Case pc.SourceType
    Case 1
      strSourceR1C1 = pc.SourceData
      strSource = Application.ConvertFormula("=" & _
          strSourceR1C1, xlR1C1, xlA1)
      strSource = Replace(strSource, "[" & wb.Name & "]", "")
      strSource = Right(strSource, Len(strSource) - 1)
      strST = "xlDatabase"
    Case Else
      strSource = "N/A"
      strST = "Other Source"
  End Select
  
  For Each wsAll In wb.Worksheets
     For Each ptAll In wsAll.PivotTables
        If ptAll.CacheIndex = pc.Index Then
           lPC = lPC + 1
        End If
     Next ptAll
  Next wsAll

  With ws
    On Error Resume Next
     ws.Range(ws.Cells(lRow, 1), _
      ws.Cells(lRow, lFields)).Value = _
       Array(pc.Index, _
        lPC, _
        pc.RecordCount, _
        strST, _
        strSource, _
        pc.RefreshDate, _
        pc.RefreshOnFileOpen)
  End With
  lRow = lRow + 1
Next pc

With ws
  With .Range(.Cells(1, 1), .Cells(1, lFields))
    .EntireRow.Font.Bold = True
    .EntireColumn.AutoFit
  End With
  .Columns(lColDate).NumberFormat _
      = "[$-409]dd-mmm-yyyy h:mm AM/PM;@"
End With
Application.EnableEvents = True

End Sub

More Pivot Cache Macros

To learn more about Excel pivot caches, and to get more pivot cache macros, go to the Excel Pivot Cache page on my Contextures website.

There are sample workbooks to download, and they contain sample data, and macros that you can test.

________________

Save