PivotTable Listing Macro

Create a list of all PivotTables in a file

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.

PT_LIst

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.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 thoughts on “PivotTable Listing Macro

  1. Unfortunately, your code does not work when there is a dynamic table with a Data Model inside the book!

    • Sorry – when I wrote this post the Data Model wasn’t used much.

      I will have to look at updating it.

      When you say a dynamic table, do mean a formatted table or a table create in DAX or Power Query?