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.