How To List All Pivot Tables From A Workbook?

[jra]

Lines below are not working but the rest are. Any solution? Thanks


.Cells(I, 6).Value = pt.TableRange1.Address
.Cells(I, 2).Value = pt.SourceData


Sub ListPivotsInfor()
'Update 20141112
Dim St As Worksheet
Dim NewSt As Worksheet
Dim pt As PivotTable
Dim I, K As Long
Application.ScreenUpdating = False
Set NewSt = Worksheets.Add
I = 1: K = 2
With NewSt
.Cells(I, 1) = "Name"
.Cells(I, 2) = "Source"
.Cells(I, 3) = "Refreshed by"
.Cells(I, 4) = "Refreshed"
.Cells(I, 5) = "Sheet"
.Cells(I, 6) = "Location"
For Each St In ActiveWorkbook.Worksheets
For Each pt In St.PivotTables
I = I + 1
.Cells(I, 1).Value = pt.Name
.Cells(I, 2).Value = pt.SourceData
.Cells(I, 3).Value = pt.RefreshName
.Cells(I, 4).Value = pt.RefreshDate
.Cells(I, 5).Value = St.Name
.Cells(I, 6).Value = pt.TableRange1.Address
Next
Next
.Activate
End With
Application.ScreenUpdating = True
End Sub

 

[6StringJazzer]

What does "not working" mean? What do you expect to happen, and what happens instead?

 

[Alex Blakenburg]

Is there any chance that you have pivot tables using the DataModel or some other external source as the Data Source ?
If that is the case you should be getting a 1004 error on this line

.Cells(I, 2).Value = pt.SourceData

If that is the case try replacing the above "pt.SourceData" line with these lines.
(it doesn't explain the address issue though, so we still need an answer to Jeff's questions)

                If pt.PivotCache.OLAP = False Then
                    .Cells(I, 2).Value = pt.SourceData
                Else 
                    Cells(I, 2).Value = pt.PivotCache.WorkbookConnection
                End If