PivotTable Listing Macro

If you inherit a file or you haven’t used a file for a while, it can be useful to do an inventory of all the PivotTables. A macro can do all the work for you.

The macro below will insert a new sheet and create the listing for you.

Just run the macro when the file with the PivotTables is selected (active). If there are no PivotTables in the file the list will be empty.

Sub PivotTableListing()
'   Creates a sheet with a list of the details of all the pivottables
'   in the current file

Dim ws As Worksheet
Dim pt As PivotTable
Dim lNextRow As Long

lNextRow = 2
Worksheets.Add
Range("A1:D1") = Array("Pivot Table Name", "Sheet Name", _
    "Report Range", "Source Data")
    
For Each ws In Worksheets

    For Each pt In ws.PivotTables

        Cells(lNextRow, 1) = pt.Name
        Cells(lNextRow, 2) = ws.Name
        Cells(lNextRow, 3) = pt.TableRange2.Address
        Cells(lNextRow, 4) = _
            Application.ConvertFormula(pt.PivotCache.SourceData, xlR1C1, xlA1)
        
        If IsError(Cells(lNextRow, 4)) Then
            Cells(lNextRow, 4) = pt.PivotCache.SourceData
        End If
        
        lNextRow = lNextRow + 1
        
    Next pt

Next ws

Cells.EntireColumn.AutoFit

Set ws = Nothing
Set pt = Nothing

End Sub

If the source data is external to the file, or a database, the details will be listed. In the case of a database the table used won’t be specified but its location will.

If you add or change the PivotTable you will need to re-run the macro.

An example of the list is shown below.

This macro is based on an idea from the book 101 Ready to Use Excel Macros by Michael Alexander published by Wiley in 2012.

The above macro is based on Macro 64 from the book and I think it is a bit simpler to understand and handles external data sources differently. It also clears the object variables at the end of the code.

Click here to see instructions on how to install and run macros.