I am working with a spreadsheet that contains 15 pivot tables, and I am
writing a macro to update them programatically. Is there a way to list ALL
the pivot tables in a sheet without actually going to them and
right-clicking, going to Table Options and seeing the name there? TIA
--
Greg Tan

Sub ListPivots()
Dim myPivot As PivotTable
Dim myCounter As Long
myCounter = 1
For Each myPivot In ActiveSheet.PivotTables
Cells(myCounter, 1).Value = myPivot.Name
myCounter = myCounter + 1
Next
End Sub

This puts the first pivot name into A1, next in A2 and so on so you may want
to adjust the code to avoid overtyping existing data!

"Greg Tan" wrote:

I am working with a spreadsheet that contains 15 pivot tables, and I am
writing a macro to update them programatically. Is there a way to list ALL
the pivot tables in a sheet without actually going to them and
right-clicking, going to Table Options and seeing the name there? TIA
--
Greg Tan

Wow that is so cool. How would I get this to write somewhere other than A1?
Thanks so much for this.
--
Greg Tan


"Martin" wrote:

Sub ListPivots()
Dim myPivot As PivotTable
Dim myCounter As Long
myCounter = 1
For Each myPivot In ActiveSheet.PivotTables
Cells(myCounter, 1).Value = myPivot.Name
myCounter = myCounter + 1
Next
End Sub

This puts the first pivot name into A1, next in A2 and so on so you may want
to adjust the code to avoid overtyping existing data!

"Greg Tan" wrote:

I am working with a spreadsheet that contains 15 pivot tables, and I am
writing a macro to update them programatically. Is there a way to list ALL
the pivot tables in a sheet without actually going to them and
right-clicking, going to Table Options and seeing the name there? TIA
--
Greg Tan

One way would be to make the Counter=20 to start at A20 or change
Cells(Counter,1) to Cells(Counter, 10) to start at J1 [Cells uses
coordinates, row then column]. If you don't want it in the sheet at all, you
could replace the whole thing with:

Sub ListPivots()
Dim myPivot As PivotTable
For Each myPivot In ActiveSheet.PivotTables
Debug.Print myPivot.Name
Next
End Sub

This will dump the list in the Immediate window (found under View in the VB
Editor).


"Greg Tan" wrote:

Wow that is so cool. How would I get this to write somewhere other than A1?
Thanks so much for this.
--
Greg Tan


"Martin" wrote:

Sub ListPivots()
Dim myPivot As PivotTable
Dim myCounter As Long
myCounter = 1
For Each myPivot In ActiveSheet.PivotTables
Cells(myCounter, 1).Value = myPivot.Name
myCounter = myCounter + 1
Next
End Sub

This puts the first pivot name into A1, next in A2 and so on so you may want
to adjust the code to avoid overtyping existing data!

"Greg Tan" wrote:

I am working with a spreadsheet that contains 15 pivot tables, and I am
writing a macro to update them programatically. Is there a way to list ALL
the pivot tables in a sheet without actually going to them and
right-clicking, going to Table Options and seeing the name there? TIA
--
Greg Tan

Thanks! I am not that great at programming, and i did not realize the
counter defaults to the first cell in the sheet. The other part (listing the
pivot tables) works awesome, this will just be the icing on the cake. Thanks
very much!
--
Greg Tan


"Martin" wrote:

One way would be to make the Counter=20 to start at A20 or change
Cells(Counter,1) to Cells(Counter, 10) to start at J1 [Cells uses
coordinates, row then column]. If you don't want it in the sheet at all, you
could replace the whole thing with:

Sub ListPivots()
Dim myPivot As PivotTable
For Each myPivot In ActiveSheet.PivotTables
Debug.Print myPivot.Name
Next
End Sub

This will dump the list in the Immediate window (found under View in the VB
Editor).


"Greg Tan" wrote:

Wow that is so cool. How would I get this to write somewhere other than A1?
Thanks so much for this.
--
Greg Tan


"Martin" wrote:

Sub ListPivots()
Dim myPivot As PivotTable
Dim myCounter As Long
myCounter = 1
For Each myPivot In ActiveSheet.PivotTables
Cells(myCounter, 1).Value = myPivot.Name
myCounter = myCounter + 1
Next
End Sub

This puts the first pivot name into A1, next in A2 and so on so you may want
to adjust the code to avoid overtyping existing data!

"Greg Tan" wrote:

I am working with a spreadsheet that contains 15 pivot tables, and I am
writing a macro to update them programatically. Is there a way to list ALL
the pivot tables in a sheet without actually going to them and
right-clicking, going to Table Options and seeing the name there? TIA
--
Greg Tan