Change source data in a pivot chart

[Guest]

Hi All

I have a stack of pivot tables each with a pivot chart. I now need to move
the location ot the Pivot tables which is the source data to the charts. But
in Chart=>Source data.. is dimmed out and i can't find a way of changing the
pivot chart's pivot table location.

Any suggestions??

TIA
jpohnb

 

[Jon Peltier]

Did you try moving the pivot table to see what would happen? I just did a
quick test with a pivot table and standalone chart. I cut the pivot table
and copied it to a different sheet, and the pivot chart knew where the pivot
table ended up.

- Jon

 

[Guest]

Hi Jon

Thanx for the reply. Like many user requests this job started out as single
pivot chart with a pivot table supplied with data from an excel query which
in turn extracted data from a Access db. But its now expanded over the years
to 30+ PC's and PT's and the associated queries are a pain to maintain. So
I've decided to use an ADO connection straight into the Access db to grab the
data and create the Pivot tables using VBA in a standard module, its works
great. I now need change the Source Data for each chart to point at the new
PT's and I can't do it! So I'd be grateful if you can cast some light on
this problem.

Kind regards
johnb

 

[Jon Peltier]

Sounds complicated. Wouldn't it be easier to ditch the old chart and create
a new one, since you're creating the PTs via VBA anyway?

- Jon

 

[Guest]

Jon

Yes. In the absence of an easy solution. I'm going to create the charts
using VB. What on earth possessed me to become an IT man??

Jon Thank you for your help

regards
johnb