How to Get the Information of all the Pivots of a Workbook into to a New Sheet
Sub ListPivotsInfo()
Dim St As Worksheet
Dim NewSt As Worksheet
Dim Pvt As PivotTable
Dim I, K As Long
Application.ScreenUpdating = False
Set NewSt = Worksheets.Add
NewSt.Name = "Pivot Info"
I = 1: K = 2
With NewSt
.Cells(I, 1) = "Pivot Name"
.Cells(I, 2) = "Data Source"
.Cells(I, 3) = "Refreshed By"
.Cells(I, 4) = "Refreshed On"
.Cells(I, 5) = "Pivot Sheet"
.Cells(I, 6) = "Pivot Location"
For Each St In ActiveWorkbook.Worksheets
For Each Pvt In St.PivotTables
I = I + 1
.Cells(I, 1).Value = Pvt.Name
.Cells(I, 2).Value = Pvt.SourceData
.Cells(I, 3).Value = Pvt.RefreshName
.Cells(I, 4).Value = Pvt.RefreshDate
.Cells(I, 5).Value = St.Name
.Cells(I, 6).Value = Pvt.TableRange1.Address
Next
Next
.Activate
End With
Application.ScreenUpdating = True
End Sub
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
Sub ListPivotsInfo()
Dim St As Worksheet
Dim NewSt As Worksheet
Dim Pvt As PivotTable
Dim I, K As Long
Application.ScreenUpdating = False
Set NewSt = Worksheets.Add
NewSt.Name = "Pivot Info"
I = 1: K = 2
With NewSt
.Cells(I, 1) = "Pivot Name"
.Cells(I, 2) = "Data Source"
.Cells(I, 3) = "Refreshed By"
.Cells(I, 4) = "Refreshed On"
.Cells(I, 5) = "Pivot Sheet"
.Cells(I, 6) = "Pivot Location"
For Each St In ActiveWorkbook.Worksheets
For Each Pvt In St.PivotTables
I = I + 1
.Cells(I, 1).Value = Pvt.Name
.Cells(I, 2).Value = Pvt.SourceData
.Cells(I, 3).Value = Pvt.RefreshName
.Cells(I, 4).Value = Pvt.RefreshDate
.Cells(I, 5).Value = St.Name
.Cells(I, 6).Value = Pvt.TableRange1.Address
Next
Next
.Activate
End With
Application.ScreenUpdating = True
End Sub
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------