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

[QuietMan]

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

 

[Roger Govier]

Hi

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

 

[QuietMan]

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

 

[Roger Govier]

Sorry, I meant Ish.name

--
Regards
Roger Govier

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

 

[QuietMan]

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