Macro to Make a List of Pivot Tables

In a complex Excel file, you might have several lists, and multiple pivot tables based on those lists. To keep things organized, use this macro to make a list of pivot tables in the active workbook. The code is shown below, and there is also a link so you can download a free workbook with the macro.

Make a List of Pivot Tables

There are two lists shown in this article – a basic list of pivot tables, and a detailed list that includes information about the source data.

The first macro makes a basic list of pivot tables in the active Excel workbook. The code checks each worksheet, and for each pivot table on the sheet, it lists the following information:

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

Make a List of Pivot Tables – The Code

The macro is listed below, and you can copy it, and paste it into a regular module in an Excel workbook. If you’re not sure how to do that, there are instructions on my Contextures website.

Note: If you don’t want to copy and paste code, there is a “List All Pivot Tables” command in my free add-in – Pivot Power Free

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

Run the Macro to Make a List of Pivot Tables

To run the macro, you can use the Macro command on the Ribbon’s View tab. There are step-by-step instructions here: Run an Excel Macro

Or, add a button on one of the sheets in your workbook, and assign the macro to that button. In the screen shot below, I added a rounded rectangle to the worksheet, and will assign the macro to that shape.

Detailed List of Pivot Tables for Troubleshooting

That simple macro will help you organize your workbook, but sometimes you need more details than that basic list provides. For example, you might see the following error message when you try to refresh your pivot tables, telling you that a field name is not valid:

“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.”

That is usually caused by blank cells in the heading row of the pivot table source data. If you know exactly which pivot table is the problem, and where its source data is located, there are instructions here on how to fix the problem.

If you don’t know which pivot table is the problem, the detailed macro might help.

Make a List of Pivot Tables – Source Data Details

If you used the Refresh All button, you might not be sure which pivot table is causing the problem. There is another macro to make a list of pivot tables, and this one also shows details about the source data.

Just like the basic macro, this code checks each worksheet, and lists the sheet name, pivot table name, cache number and source data. To help with your troubleshooting, the list also shows the following details about the source date (for data that on a worksheet in the same Excel file):

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

Then, go to the pivot tables that need the headings fixed, or that haven’t refreshed, and fix the problems.

Get the Macros to Make a List of Pivot Tables

You can download a free workbook from my Contextures website, and get both macros in that workbook. The file also has multiple pivot tables and source data tables, so you can test the macros, to see how they work.

Go to the Excel Pivot Table List Macros page, and click the link in the “Download the Workbook” section. The zipped file is in xlsm format, and contains macros. Enable macros when you open the workbook, if you want to test the macros.

_______________________

Save

Save