Creating Multiple Charts from Same Pivot Table using macro

[Guest]

Hi
I have been writing a macro to process/reformat multiple data streams into a
single table for analysis. Analysis is driven by pivot table and associated
pivot chart. What I would like to do is to generate several chart views in
sequence, grabbing a copy or picture of each chart to form a standard report.

Problem - Excel does not allow copy/pastespecial/picture type actions on a
PivotChart.

Possible solutions with issues
1 It does not seem efficient to create the pivot table, pivot chart and
then delete the associated pivot table (as I understand 'Help' to be telling
me I should) just to get the picture.
2 Creating multiple instances of the pivot table with different field
selections and a pivot chart associated with instance does not seem to
efficient either.

Can anyone suggest a better way?

Thanks

 

[Guest]

Hi,

You can copy the pivot chart as a picture in VBA. Here's an example:

Sub test()
'copy Chart1 as picture
Sheet1.ChartObjects("Chart 1").CopyPicture
'paste it in Sheet2 A1
Sheet2.Paste Range("A1")
End Sub

 

[Guest]

Thanks for the sample code. Sorry I have not been able to try it today, but
it is on my sheet for Friday.

 

[Jon Peltier]

You could copy the pivot table, make the chart, and delete the copy of the
pivot table.

- Jon

 

[Guest]

Thanks - in the process of writing out my problem I thought of that approach
and have implemented that solution although it may not be the most efficient
approach - it works. I am also going to try the suggestion from Vergel
Adriano.

 

[Guest]

Vergel

I have tried this method, but have not been able to make it work. Could be
due to my lack of detailed knowledge of VBA as I tend to 'work from example'
even if that means recording similar code then working through the
adaptations.

I could not find the name of the chart (corresponding to your 'Chart1') so I
recorded a macro and tried replacing your statement with
ActiveChart.ChartArea.CopyPicture

This returned an error 438 - object does not support this property or method

I have solved my current problem a different way - create pivot chart then
delete the pivot table that is behind it.

Thanks