Create PivotChart from PivotTable

[mattty]

How can I create a PivotChart using VBA? I ran the macro recorder, and it referred to the source data in the standard way:
ActiveChart.SetSourceData Source:=Range("'sheet1'!$A$3:$C$25")

How do I create a chart that is specifically a PivotChart, referring to a PivotTable as a source, so that when the PT updates, so does the PC?

Seems like it should be a simple solution, but I have had no luck with this.
Thanks in advance!

PS I am using Excel 2007, but I don't think that should matter

 

[mattty]

bump.

sorry, can anyone help with this? I can think of a bunch of workarounds, but I'm sure that there must be some simple, more elegant, solution.

thanks.

 

[DonkeyOte]

When you created the PT you could have created as PT Chart -- doing so would generate both PT & PT Chart simultaneously (I think) -- untested... try recreating.

I'll test myself in a bit...

EDIT: yes, creating as Pivot Chart in first instance will generate the PT -- thus the two are tied... altering one alters the other... so you should find your problem disappears.

 

[mattty]

Hey, thanks for the help. I will try it out and get back to you.

 

[mattty]

Yes, this method works, but it still refers to a PivotTable by its range, rather than by its name. Also, you need to be inserting PTs to begin with (mine already exist).

The solution I used - for the benefit of anyone reading this later on - was to create blank charts, then use code to fill them from the PTs.