How can I locate Pivot tables in a workbook

[AndyDevine1]

hi there

There is code to update pivot tables in a workbook I've inherited

Running this is causing an error as the tables cannot locate the source data

I've gone through the many pivot tables and re-pointed them to the correct data source

When I re-run the code it is still telling me there are some pivot tables where the data source cannot be found.

I've no hidden worksheets

Is there a way I can locate the remaining pivot tables in order to fix the problem?

Many thanks

 

[sbhat]

Thank you - this helped me locate a generically-named pivot table within 3 tabs of dozens of pivots!

In normal case of pivot tables based on cell-ranges this piece of code works. However, when a pivot table is based on a data model, the code throws the error: 1004-object not defined. What is the remedy? In fact, the pivottable.Sourcedata fails when it encounters a data model.

 

[Ragnar1211]

I haven't posted here in ages... but try this:

Sub PivotID()
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim pt As PivotTable
    Dim ptSource As String
    
    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
                If pt.DataModelTable Is Nothing Then
                    ptSource = pt.SourceData
                Else
                    ptSource = "Data Model: " & pt.DataModelTable.Name
                End If
                
                MsgBox pt.Name & " source data is " & ptSource
            Next
        End If
    
    Next

End Sub

 

[sbhat]

I haven't posted here in ages... but try this:

Sub PivotID()
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim pt As PivotTable
    Dim ptSource As String
   
    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
                If pt.DataModelTable Is Nothing Then
                    ptSource = pt.SourceData
                Else
                    ptSource = "Data Model: " & pt.DataModelTable.Name
                End If
               
                MsgBox pt.Name & " source data is " & ptSource
            Next
        End If
   
    Next

End Sub

Thank you for your response. Upon execution of the code you provided, an error is encountered: "Run-time error '438': Object doesn't support this property or method."; the error pointed to the line = "

If pt.DataModelTable Is Nothing Then

Anyway, I have the best code available at this point which would at least provide me Source Data info for normal pivots which are not based on data model.