Excel XP: Pivot Charts "source"

[tallbarb]

I have inherited a workbook with pivot tables & charts. I can find the data source for the tables with no problem but how do I find out the source for the pivot charts? There is no way to run the wizard and go backwards as with the pivot table. The only time I ever saw reference to a pivot chart's source was when I clicked the refresh button and the data it was based on was "missing" and the name of the sheet appeared in the error message. Is there an EASY way?? Thanks so much!!

 

[SkipVought]

Hi,

There is a PivotTable as source for the PicotChart.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[tallbarb]

Yes - I know - but how do I find out which pivot table is the source for the chart? There are 80 sheets and all the charts are together and all the pivot tables are together and all the source data is together. I can't assume by location that a chart is "next to" the pivot table it's based on.

 

[SkipVought]

Sub PChart()
   For Each sht In Sheets
      If sht.Type = 3 Then
         For Each sc In sht.SeriesCollection
            MsgBox sht.Name & ":" & sc.Formula
         Next
      End If
   Next
End Sub

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[xlbo]

If you right click on one of the FIELDS in the pivotchart rather than the chart itself, you get the PivotCHART options rather than the standard chart options. From there, choose "Table Properties" - in which you can see the name of the pivottable it is attached to

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[tallbarb]

Since I am not a programmer, I have no idea what this code will do - other than a message box is supposed to display with info in it. I copied & pasted it into the VBA window, but nothing seems to happen. Can you explain this so I am using it correctly? Thanks!

 

[tallbarb]

Thanks, Geoff - that certainly does work. I can match up "Pivot Table 8" by looking at all pivot tables and finding the one that matches that name. I am disappointed there isn't an easier way. I was hoping for a reference to the sheet tab name and a range of data - which is what I see when I use the Wizard to go back and view the data source for the pivot table. I guess I should be glad I can find out which chart goes with which table. Just don't like to have to spend that much time looking at each pivot table name in order to match it with its chart. What a drag! Anyway - that's just life in Office land.

Thanks for your help!!!

 

[SkipVought]

What is it that you want to do with this information?

Put it on a sheet to display the relationships?

Sub PChart()
   Dim wsNew As Worksheet
   Set wsNew = Worksheets.Add
   r = 1
   With wsNew
      .Cells(r, 1).Value = "Chart Name"
      .Cells(r, 2).Value = "Source Sheet"
      .Cells(r, 3).Value = "Source Range"
      For Each sht In Sheets
         If sht.Type = 3 Then
            For Each sc In sht.SeriesCollection
               r = r + 1
               .Cells(r, 1).Value = sht.Name
               .Cells(r, 2).Value = Split(Split(sc.Formula, "!")(0), "(")(1)
               .Cells(r, 3).Value = Split(Split(sc.Formula, "!")(1), ",")(0)
            Next
         End If
      Next
   End With
End Sub

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[SkipVought]

I realize that the above code only gives you the range of the label. THis code gives you the Category & Value ranges

Sub PChart()
   Dim wsNew As Worksheet
   Set wsNew = Worksheets.Add
   r = 1
   With wsNew
      .Cells(r, 1).Value = "Chart Name"
      .Cells(r, 2).Value = "Source Sheet"
      .Cells(r, 3).Value = "Source Category Range"
      .Cells(r, 4).Value = "Source Value Range"
      For Each sht In Sheets
         If sht.Type = 3 Then
            For Each sc In sht.SeriesCollection
               r = r + 1
               .Cells(r, 1).Value = sht.Name
               .Cells(r, 2).Value = Split(Split(sc.Formula, "!")(0), "(")(1)
               .Cells(r, 3).Value = Split(Split(sc.Formula, "!")(2), ",")(0)
               .Cells(r, 4).Value = Split(Split(sc.Formula, "!")(3), ",")(0)
            Next
         End If
      Next
   End With
End Sub

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]