VBA code to list all pivot tables in a workbook!!!

Pivot table is one of the powerful data interpretation tool in excel. Which is used to summarise large amount of data at a glance. It is mostly used in various reporting task. Because of this pivots are extensively used in the excel reports and dashboards. Thus, there will be various pivots tables can be used in excel workbook. In order to validate pivot tables availability in a workbook someone needs list of all pivot tables. Obviously, to collect list of all pivot tables again result into a manual task which can be time consuming. But, this can be automate by a excel macro.

  
Here is the excel macro which will give us list of all pivot in a workbook: 

Option Explicit

Sub List_All_Pivots()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com

Dim pt As PivotTable
Dim ws As Worksheet
Dim strPvt As String
Dim i As Integer

strPvt = ""
i = 1

'For loop through each worksheets
    For Each ws In ActiveWorkbook.Worksheets
        Worksheets(ws.Name).Select

'Loop to select each pivot table for a single worksheet
        For Each pt In ws.PivotTables
           
            'Pass Pivot names to variable in order to show in message. comment or delete this if not required
            strPvt = strPvt & i & ".  " & pt.Name & vbNewLine
            
            'Uncomment below line to get pivot table list in sheet1
            'ActiveWorkbook.Sheets("Sheet1").Range("A" & i).Value = pt.Name
            i = i + 1
            
        Next pt
    Next ws
    
MsgBox "List of Pivot Tables as Below: " & vbNewLine & vbNewLine & strPvt, vbInformation, "Pivot List"

End Sub