Copying Multiple Charts as a Picture in Excel 2007 gives Application-defined error

Question

Introduction

I can't seem to get the the ChartObjects.CopyPicture method to work in Excel 2007. No matter what I try I get an error.

Using this technique throws an 'Application-defined or object-defined error' on the CopyPicture line.

ActiveSheet.ChartObjects.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Sheets("Sheet2").Paste

This method throws as 'PasteSpecial method of Worksheet class failed' on the PasteSpecial line

ActiveSheet.ChartObjects.Copy    
Sheets("Sheet2").PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False

However, If I use the chart as a Shape object it works.

ActiveSheet.Shapes("Chart 6").CopyPicture Appearance:=xlScreen, Format:=xlPicture
Sheets("Sheet2").Paste

This also works

ActiveSheet.Shapes("Chart 6").Copy
Sheets("Sheet2").PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False

Problem

My problem is that when I try to copy multiple charts as a group this fails.

I tried to use the Range property of the Shapes object, but there is no CopyPicture method available. I came up with this work-around, but this also failed, with the same messages that I got when trying to CopyPicture.

ActiveSheet.Shapes.Range(Array("Chart 5", "Chart 6")).Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Sheets("Sheet2").Paste

And conversely this didn't work either

ActiveSheet.Shapes.Range(Array("Chart 5", "Chart 6")).Select
Selection.Copy
Sheets("Sheet2").PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False

Answer 1

This issue has been driving me crazy for a long time. I finally found a solution that works, hopefully this will help someone else in the future.

Basically the solution was to Group the Charts as a single Shape object, then CopyPicture on that shape, then Ungroup when your finished.

With ActiveSheet.ChartObjects.ShapeRange.Group
    .CopyPicture Appearance:=xlScreen, Format:=xlPicture
    .Ungroup
End With

Sheets("Sheet2").Paste

Answer 2

Thanks for posting... your use of ShapeRange was very helpful with another problem... copying charts from Excel to Word (sometimes had missing data).

Answer 3

saved my sanity! thanks!