If you inherit a file or you haven’t used a file for 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.