List of Pivot Tables and info

[ldc]

This worked great to list the pivot tables. Anything to show location of the
pivot table or the data range the pivot table is using?

 

[Max]

This worked great to list the pivot tables.
Anything to show location of the pivot table
or the data range the pivot table is using?

Hi, it's me again. I tinkered around with the macro recorder, and came up
with this slight revision to Charles' sub which seems to work ok
(PT.SourceData yields the desired source data range)

Sub ListPivotTablesNSource()
Dim PT As PivotTable
Dim Sh As Worksheet
Dim lSh As Worksheet 'Loop Sheet
Set Sh = Worksheets.Add
Sh.Range("A1") = "Pivot Table Name"
Sh.Range("B1") = "Pivot Table Source"
For Each lSh In ActiveWorkbook.Worksheets
For Each PT In lSh.PivotTables
With Sh.Range("A" & Sh.Rows.Count).End(xlUp)
.Offset(1, 0) = PT.Name
.Offset(1, 1) = PT.SourceData
End With
Columns("A:B").EntireColumn.AutoFit
Next
Next
End Sub


---

 

[ldc]

PERFECT!

Hi, it's me again. I tinkered around with the macro recorder, and came up
with this slight revision to Charles' sub which seems to work ok
(PT.SourceData yields the desired source data range)

Sub ListPivotTablesNSource()
Dim PT As PivotTable
Dim Sh As Worksheet
Dim lSh As Worksheet 'Loop Sheet
Set Sh = Worksheets.Add
Sh.Range("A1") = "Pivot Table Name"
Sh.Range("B1") = "Pivot Table Source"
For Each lSh In ActiveWorkbook.Worksheets
For Each PT In lSh.PivotTables
With Sh.Range("A" & Sh.Rows.Count).End(xlUp)
.Offset(1, 0) = PT.Name
.Offset(1, 1) = PT.SourceData
End With
Columns("A:B").EntireColumn.AutoFit
Next
Next
End Sub


---

 

[Max]

Welcome, glad to hear that.

 

[ryguy7272]

This is a pretty handy tool:
http://www.contextures.com/xlPivotPlayPLUS01.html

Regards,
Ryan---