The macro below will analyse your Excel workbook and create a list of pivot tables and their attributes in a new sheet. This is a great tool for auditing complex Excel spreadsheets containing multiple pivot tables.
Sub list_Pivots()
Dim pvt As PivotTable
Dim iSht As Long
Dim iRow As Integer
Application.ScreenUpdating = False
Set objNewSheet = Worksheets.Add
objNewSheet.Activate
iRow = 2
iSht = 2
'SET TITLES
Range("A1").FormulaR1C1 = "Name"
Range("B1").FormulaR1C1 = "Source"
Range("C1").FormulaR1C1 = "Refreshed by"
Range("D1").FormulaR1C1 = "Refreshed"
Range("E1").FormulaR1C1 = "Sheet"
Range("F1").FormulaR1C1 = "Location"
'GET PIVOT DETAILS
Do While iSht <= Worksheets.Count
Sheets(iSht).Select
For Each pvt In ActiveSheet.PivotTables
objNewSheet.Cells(iRow, 1).Value = pvt.Name
objNewSheet.Cells(iRow, 2).Value = pvt.SourceData
objNewSheet.Cells(iRow, 3).Value = pvt.RefreshName
objNewSheet.Cells(iRow, 4).Value = pvt.RefreshDate
objNewSheet.Cells(iRow, 5).Value = ActiveSheet.Name
objNewSheet.Cells(iRow, 6).Value = pvt.TableRange1.Address
iRow = iRow + 1
Next
iSht = iSht + 1
Loop
objNewSheet.Activate
Application.ScreenUpdating = True
End Sub
