List of Pivots

  • Thread starter Thread starter Brent
  • Start date Start date
B

Brent

Can someone write code for me that would list all of the
pivot table names in my workbook and the reference?
There's a built in utility in Excel that does this for
named ranges. Thx.
 
The following code lists the pivot tables and sheet names on a worksheet
named PivotList:

'===========================
Sub CreatePivotList()
Dim ws As Worksheet
Dim pt As PivotTable
Dim wsPL As Worksheet
Set wsPL = Worksheets("PivotList")
Dim r As Long

wsPL.Cells.Clear
wsPL.Cells(1, 1).Value = "Worksheet"
wsPL.Cells(1, 2).Value = "PT Name"

For Each ws In ActiveWorkbook.Worksheets
r = wsPL.Cells(Rows.Count, 1).End(xlUp).Row + 1
For Each pt In ws.PivotTables
wsPL.Cells(r, 1).Value = ws.Name
wsPL.Cells(r, 2).Value = pt.Name
r = r + 1
Next pt
r = r + 1
Next ws

End Sub
'=========================
 
Back
Top