Pivot Table List Macros

Use macros to create a list of pivot tables in the active workbook, with key information about each pivot table. Helps with troubleshooting Excel pivot tables. Also see: List all Pivot Fields with Details

List All Pivot Tables - Basic List

Use this macro to create a list of all the pivot tables in the active workbook.

The code checks each worksheet, and for each pivot table on that sheet, it lists the following information:

• Worksheet name
• Pivot Table name
• Pivot Cache index number
• Source Data name or range address

Sub ListWbPTsBasic()
Dim ws As Worksheet
Dim pt As PivotTable
Dim wsPL As Worksheet
Dim RowPL As Long
Dim RptCols As Long
On Error Resume Next

RptCols = 4
Set wsPL = Worksheets.Add
RowPL = 2

With wsPL
  .Range(.Cells(1, 1), .Cells(1, RptCols)).Value _
    = Array("Worksheet", _
        "PT Name", _
        "PivotCache", _
        "Source Data")
End With

For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
     With wsPL
        .Range(.Cells(RowPL, 1), _
            .Cells(RowPL, RptCols)).Value _
          = Array(ws.Name, _
              pt.Name, _
              pt.CacheIndex, _
              pt.SourceData)
      End With
     RowPL = RowPL + 1
   Next pt
Next ws

With wsPL
  .Rows(1).Font.Bold = True
  .Range(.Cells(1, 1), .Cells(1, RptCols)) _
      .EntireColumn.AutoFit
End With

End Sub

List All Pivot Tables - Headings

Use this macro to create a list of all the pivot tables in the active workbook, with details on the source data. This can help if you get an error message when refreshing pivot tables, such as:

"The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field."

The code checks each worksheet, and for each pivot table on that sheet, it lists the following information:

• Worksheet name
• Pivot Table name
• Pivot Cache index number
• Source Data name or range address

Also, if the source data is a list in the same Excel workbook, it shows the following details about the source date:

• Number of records
• Number of columns
• Number of heading cells that contain values
• Fix -- an X if number of columns does not match number of headings
• Latest refresh date for the pivot cache

Sub ListWbPTsHeads()
Dim ws As Worksheet
Dim wsSD As Worksheet
Dim lstSD As ListObject
Dim pt As PivotTable
Dim wsPL As Worksheet
Dim rngSD As Range
Dim rngHead As Range
Dim RowPL As Long
Dim RptCols As Long
Dim SDCols As Long
Dim SDHead As Long
Dim lBang As Long
Dim nm As Name
Dim strSD As String
Dim strRefRC As String
Dim strRef As String
Dim strWS As String
Dim strAdd As String
Dim strFix As String
On Error Resume Next

RptCols = 9
RowPL = 2

Set wsPL = Worksheets.Add

With wsPL
  .Range(.Cells(1, 1), .Cells(1, RptCols)).Value _
    = Array("Worksheet", _
        "PT Name", _
        "PivotCache", _
        "Source Data", _
        "Records", _
        "SD Cols", _
        "SD Heads", _
        "Fix", _
        "Refreshed")
End With

For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
    If pt.PivotCache.SourceType = 1 Then  'xlDatabase
      Set nm = Nothing
      strSD = ""
      strAdd = ""
      strFix = ""
      SDCols = 0
      SDHead = 0
      Set rngHead = Nothing
      Set lstSD = Nothing
      
      strSD = pt.SourceData
      
      'worksheet range?
      lBang = InStr(1, strSD, "!")
      If lBang > 0 Then
        strWS = Left(strSD, lBang - 1)
        strRefRC = Right(strSD, Len(strSD) - lBang)
        strRef = Application.ConvertFormula( _
              strRefRC, xlR1C1, xlA1)
        Set rngSD = Worksheets(strWS).Range(strRef)
        SDCols = rngSD.Columns.Count
        Set rngHead = rngSD.Rows(1)
        SDHead = WorksheetFunction.CountA(rngHead)
        GoTo AddToList
      End If
      
      'named range?
      Set nm = ThisWorkbook.Names(strSD)
      If Not nm Is Nothing Then
        strAdd = nm.RefersToRange.Address
        SDCols = nm.RefersToRange.Columns.Count
        Set rngHead = nm.RefersToRange.Rows(1)
        SDHead = WorksheetFunction.CountA(rngHead)
        GoTo AddToList
      End If
      
      'list object?
        For Each wsSD In ActiveWorkbook.Worksheets
          Set lstSD = wsSD.ListObjects(strSD)
          If Not lstSD Is Nothing Then
            strAdd = lstSD.Range.Address
            SDCols = lstSD.Range.Columns.Count
            Set rngHead = lstSD.HeaderRowRange
            SDHead = WorksheetFunction.CountA(rngHead)
            GoTo AddToList
          End If
        Next
    End If
    
AddToList:
     If SDCols <> SDHead Then strFix = "X"
     With wsPL
        .Range(.Cells(RowPL, 1), _
            .Cells(RowPL, RptCols)).Value _
          = Array(ws.Name, _
              pt.Name, _
              pt.CacheIndex, _
              pt.SourceData, _
              pt.PivotCache.RecordCount, _
              SDCols, _
              SDHead, _
              strFix, _
              pt.PivotCache.RefreshDate)
      End With
     RowPL = RowPL + 1
   Next pt
Next ws

With wsPL
  .Rows(1).Font.Bold = True
  .Range(.Cells(1, 1), .Cells(1, RptCols)) _
      .EntireColumn.AutoFit
End With

End Sub

Download Free Workbook

To see how the macros work, and to get the sample code, download the Pivot Table List Macros workbook. The zipped file is in xlsm format, and contains macros. Enable macros when you open the workbook, if you want to test the macros.

More Tutorials

Pivot Table Field List Macros

List all Pivot Fields with Details

FAQs - Pivot Tables

Pivot Table Introduction

Pivot Table Blog

Pivot Table Article Index