Excel VBA - Copy a chart and paste as an enhanced metafile

Question

I'm trying to copy Chart1 that is an object in Book1 Sheet1 and paste it as an enhanced metafile picture in Book2 Sheet1. I got this code from another site, but it doesn't work:

'Book1 and Book2 previously declared as Workbooks and set
Book1.Sheets(1).ChartObjects(1).CopyPicture
Book2.Sheets(1).Range("B3").Paste

I get Run-time error 438: "Object doesn't support this property or method"

I would use something like this, but their code references the chart location and I would like to go by the chart number if possible.

UPDATE:

I have revised my code to the following:

'Sheet1 and Sheet2 declared and set as Worksheets
Sheet1.ChartObjects(1).Chart.CopyPicture
Sheet2.PasteSpecial Format:="Picture (Enhanced Metafile)", _
    Link:=False, DisplayAsIcon:=False
With Sheet2.Shapes(Sheet2.Shapes.Count)
    .Top = Sheet2.Range("B3").Top
    .Left = Sheet2.Range("B3").Left
End With

however it now returns Run-time Error 1004: "Method 'PasteSpecial' of object '_Worksheet' failed".

Umm, that is strange. It works for me. BTW 'Sheet1 and Sheet2 declared and set as Worksheets Um, why would you declare them unless you use something like Sh1.ChartObjects(1).Chart.CopyPicture — Siddharth Rout
@SiddharthRout - That is exactly what I'm doing. Next time, I'll call them SheetA and SheetB to lessen the confusion? — Kes Perron
@SMPerron - Sheet1 in my example code wasn't a variable I'd declared, but the existing default codename of "Sheet1" in the workbook containing the code. By default you can refer to a sheet in the same workbook using either (eg) ThisWorkbook.Sheets("Sheet1") or just Sheet1. Your revised code works for me (but I avoided declaring variables named Sheet1, Sheet2 for reasons outlined above). — Tim Williams
Not sure why it's working for you guys and not me. It works if I change it from PasteSpecail to just Paste, but the picture quality degrades. — Kes Perron

Tim Williams Tim Williams · Accepted Answer · 2013-11-12T17:52:35

For example:

Sheet1.ChartObjects(1).CopyPicture
Sheet1.PasteSpecial _
         Format:="Picture (Enhanced Metafile)", _
         Link:=False, DisplayAsIcon:=False
With Sheet1.Shapes(Sheet1.Shapes.Count)
    .Top = Sheet1.Range("B3").Top
    .Left = Sheet1.Range("B3").Left
End With

Excel VBA - Copy a chart and paste as an enhanced metafile

1 Answers