Creating Multiple Charts from Same Pivot Table using macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Thanks for the sample code. Sorry I have not been able to try it today, but
it is on my sheet for Friday.
 
You could copy the pivot table, make the chart, and delete the copy of the
pivot table.

- Jon
 
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.
 
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
 
Back
Top