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

 

[RoryA]

So what's the problem?

 

[AndyDevine1]

I was very pleased to see I had a response. Shame because other people may think that the one reply I've had means its been resolved when it hasnt...

Have I been unclear or have you not read my post properly?!?

 

[AndyDevine1]

I've been through every sheet and changed the data source for all of the pivot tables I can locate

When I re-run the code it doesnt work as there are still some pivot tables I need to change it would appear

Is there a way I run a code that tells me where all of the pivot tables are in order for me to fix the problem?

 

[RoryA]

Your post seems to have changed since I posted my reply.

 

[AndyDevine1]

Hi Rory
Can you help me...?

You're not being very helpful at the moment to be honest...with your comments

I've inherited a workbook with a refresh pivot table code in it (code is written based on the 'for each worksheet in workbook' method...

When I ran it it doesnt work as it cant find the data source for some of the pivot tables

But I cant seem to locate all of the pivot tables in the workbook (I've changed the ones I can locate)

What can i do?

 

[RoryA]

You can:
1. Avoid sarcastic responses to people you'd like to help you.
2. Use the code Fazza posted here: http://www.mrexcel.com/forum/excel-...-pivot-tables-large-multi-sheet-workbook.html

 

[AndyDevine1]

Its not me being sarcastic, its me being annoyed/frustrated as I didn't experience you wanting to help.

But thank you very much for the tip, I will look into this....

 

[Ragnar1211]

Try this:

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

 

[StephenD]

Try this:

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

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