How to list all PivotTables in a workbook using VBA?

To list all PivotTables in a workbook using VBA, you can create a macro that loops through each worksheet and each PivotTable on those sheets. Here’s a simple script to achieve that:

Sub ListAllPivotTables()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim row As Integer
    Dim outputSheet As Worksheet

    ' Create a new worksheet to output the list of PivotTables
    Set outputSheet = ThisWorkbook.Worksheets.Add
    outputSheet.Name = "PivotTableList"

    ' Set headers for the output
    With outputSheet
        .Cells(1, 1).Value = "Worksheet Name"
        .Cells(1, 2).Value = "Pivot Table Name"
        .Cells(1, 3).Value = "Pivot Table Address"
    End With

    ' Initialize the row for output
    row = 2

    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Loop through each PivotTable in the worksheet
        For Each pt In ws.PivotTables
            ' Output the worksheet name, PivotTable name, and address
            With outputSheet
                .Cells(row, 1).Value = ws.Name
                .Cells(row, 2).Value = pt.Name
                .Cells(row, 3).Value = pt.TableRange2.Address
            End With
            row = row + 1
        Next pt
    Next ws

    ' Autofit the columns in the output sheet for better readability
    outputSheet.Columns("A:C").AutoFit
    
    MsgBox "PivotTable list has been created in sheet: " & outputSheet.Name
End Sub

How it works:

Initialize Worksheet and PivotTable Variables: The macro iterates over every worksheet and each PivotTable on those sheets.
Create an Output Sheet: It creates a new worksheet named “PivotTableList” to store the details.
Headers: Sets up headers in the output worksheet to describe the data (worksheet name, PivotTable name, and address).
Loop through Each Worksheet and PivotTable: Populates the relevant details into the output sheet.
Autofit Columns: Makes sure the columns are adjusted to fit the content for clear viewing.

Usage:

Open your Excel workbook.
Press `Alt` + `F11` to open the VBA editor.
Go to `Insert` > `Module` to create a new module.
Copy and paste the above code into the module.
Close the VBA editor and run the macro (`Alt` + `F8`) and select `ListAllPivotTables`.

This will create a new worksheet with a list of all PivotTables in your workbook, including their locations.

facebooklinkedin