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://www.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