Sub PivotID()
Dim wb As Workbook
Dim sh As Worksheet
Dim pt As PivotTable
Set wb = ActiveWorkbook
For Each sh In wb.Worksheets
MsgBox sh.Name & " has " & sh.PivotTables.Count & " pivot tables"
If sh.PivotTables.Count > 0 Then
For Each pt In sh.PivotTables
MsgBox pt.Name & " source data is " & pt.SourceData
Next
End If
Next
End Sub
===========
The above code works in normal course when pivot tables are based on cell-ranges; however, when a pivot table is based on data model the pt.SourceData throws error 1004: object not defined. What is the remedy?
Dim wb As Workbook
Dim sh As Worksheet
Dim pt As PivotTable
Set wb = ActiveWorkbook
For Each sh In wb.Worksheets
MsgBox sh.Name & " has " & sh.PivotTables.Count & " pivot tables"
If sh.PivotTables.Count > 0 Then
For Each pt In sh.PivotTables
MsgBox pt.Name & " source data is " & pt.SourceData
Next
End If
Next
End Sub
===========
The above code works in normal course when pivot tables are based on cell-ranges; however, when a pivot table is based on data model the pt.SourceData throws error 1004: object not defined. What is the remedy?