How can I locate Pivot tables in a workbook

AndyDevine1

Board Regular
Joined
Jun 1, 2006
Messages
172
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
So what's the problem?
 

AndyDevine1

Board Regular
Joined
Jun 1, 2006
Messages
172
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

Board Regular
Joined
Jun 1, 2006
Messages
172
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

AndyDevine1

Board Regular
Joined
Jun 1, 2006
Messages
172
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?
 

AndyDevine1

Board Regular
Joined
Jun 1, 2006
Messages
172
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

Well-known Member
Joined
Jul 10, 2008
Messages
571
Try this:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,994
Messages
5,526,125
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top