List of Pivot Tables and info
 

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" wrote:

"ldc" wrote:
Is there a function similar to "Insert-Name-Paste-Paste List" for pivot
tables, where I can generate a list of all pivot tables and where they are
located in the spreadsheet?

A google search revealed a sub by Charles Chickering, pasted below:
-------------------
Sub ListPivotTables()
Dim PT As PivotTable
Dim Sh As Worksheet
Dim lSh As Worksheet 'Loop Sheet
Set Sh = Worksheets.Add
Sh.Range("A1") = "Pivot Table Name"
For Each lSh In ActiveWorkbook.Worksheets
For Each PT In lSh.PivotTables
Sh.Range("A" & Sh.Rows.Count).End(xlUp).Offset(1) = PT.Name
Next
Next
End Sub
--
Charles Chickering
"A good example is twice the value of good advice."
"nj" wrote:
I've for a TOC for the sheet and can use "Paste List" to get a list of
named ranges, but can someone point me towards the code for generating
a list of the pivot tables in a file?

I'm needing to go thru the file clean up the naming conventions and
then update the subroutines to use the updated names.

Something comparable to ActiveWorkbook.Sheets.Count, I guess?
-----------
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

List of Pivot Tables and info
 

"ldc" wrote:
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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

List of Pivot Tables and info
 

PERFECT!

"Max" wrote:

"ldc" wrote:
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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

List of Pivot Tables and info
 

Welcome, glad to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ldc" wrote in message
...
PERFECT!

List of Pivot Tables and info
 

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

Regards,
Ryan---


--
RyGuy


"Max" wrote:

"ldc" wrote:
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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---