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
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.