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
--
KenY

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


--
Hope that helps.

Vergel Adriano


"KenY" wrote:

> 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
> --
> KenY

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


"Vergel Adriano" wrote:

> 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
>
>
> --
> Hope that helps.
>
> Vergel Adriano
>
>
> "KenY" wrote:
>
> > 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
> > --
> > KenY

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

"KenY" <(E-Mail Removed)> wrote in message
news:69CB4A7D-AF6C-4A5F-8180-(E-Mail Removed)...
> 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
> --
> KenY

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.

--
KenY


"Jon Peltier" wrote:

> You could copy the pivot table, make the chart, and delete the copy of the
> pivot table.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>
> "KenY" <(E-Mail Removed)> wrote in message
> news:69CB4A7D-AF6C-4A5F-8180-(E-Mail Removed)...
> > 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
> > --
> > KenY
>
>
>

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
--
KenY


"Vergel Adriano" wrote:

> 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
>
>
> --
> Hope that helps.
>
> Vergel Adriano
>
>
> "KenY" wrote:
>
> > 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
> > --
> > KenY