Pivot Table..getting a list of all, sourec odata and sheets locate
 

Can some one help me determine the sheet name that each pivot table is located

I'm using the code below

thanks

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"
Sh.Range("C1") = "Pivot Table Location"
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
Next
Next

Columns("A:B").EntireColumn.AutoFit
End Sub


--
Helping Is always a good thing

Pivot Table..getting a list of all, source data and sheets locate
 

Hi

Isn't it just
..Offset(1,2) = Sh.name

--
Regards
Roger Govier

"QuietMan" wrote in message
...
Can some one help me determine the sheet name that each pivot table is
located

I'm using the code below

thanks

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"
Sh.Range("C1") = "Pivot Table Location"
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
Next
Next

Columns("A:B").EntireColumn.AutoFit
End Sub


--
Helping Is always a good thing

Pivot Table..getting a list of all, source data and sheets loc
 

NBo...that would only get me the name of the sheet that i'm listing the pivot
table information on...I need the name of the sheet where the pivot table
located
--
Helping Is always a good thing


"Roger Govier" wrote:.

Hi

Isn't it just
.Offset(1,2) = Sh.name

--
Regards
Roger Govier

"QuietMan" wrote in message
...
Can some one help me determine the sheet name that each pivot table is
located

I'm using the code below

thanks

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"
Sh.Range("C1") = "Pivot Table Location"
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
Next
Next

Columns("A:B").EntireColumn.AutoFit
End Sub


--
Helping Is always a good thing

Pivot Table..getting a list of all, source data and sheets loc
 

Sorry, I meant Ish.name

--
Regards
Roger Govier

"QuietMan" wrote in message
...
NBo...that would only get me the name of the sheet that i'm listing the
pivot
table information on...I need the name of the sheet where the pivot table
located
--
Helping Is always a good thing


"Roger Govier" wrote:.

Hi

Isn't it just
.Offset(1,2) = Sh.name

--
Regards
Roger Govier

"QuietMan" wrote in message
...
Can some one help me determine the sheet name that each pivot table is
located

I'm using the code below

thanks

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"
Sh.Range("C1") = "Pivot Table Location"
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
Next
Next

Columns("A:B").EntireColumn.AutoFit
End Sub


--
Helping Is always a good thing

Pivot Table..getting a list of all, source data and sheets loc
 

Thanks...It's a little funny...I should not us "l" as like you I mistaked it
for i, which is why the code would not work...

Thanks
--
Helping Is always a good thing


"Roger Govier" wrote:

Sorry, I meant Ish.name

--
Regards
Roger Govier

"QuietMan" wrote in message
...
NBo...that would only get me the name of the sheet that i'm listing the
pivot
table information on...I need the name of the sheet where the pivot table
located
--
Helping Is always a good thing


"Roger Govier" wrote:.

Hi

Isn't it just
.Offset(1,2) = Sh.name

--
Regards
Roger Govier

"QuietMan" wrote in message
...
Can some one help me determine the sheet name that each pivot table is
located

I'm using the code below

thanks

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"
Sh.Range("C1") = "Pivot Table Location"
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
Next
Next

Columns("A:B").EntireColumn.AutoFit
End Sub


--
Helping Is always a good thing