Cannot copy charts using VBA

Question

I'm trying to copy a chart objects from one sheet to another.  When I do that manually in Excel 2010, using Ctrl-C and Ctrl-V (copy and paste) it works fine.  When I do that using VBA in Access 2003/Excel 2003 it works.  The result is a chart object.

When I try that in Access 2010/Excel 2010 I get an error. 

run-time error "-2147417851 (80010105) Method 'Copy' of object 'ChartObject' failed

The code is

For Each ch In xlChart.ChartObjects ch.Copy

If I first activate the chart in 2010 I no longer get an error.

For Each ch In xlChart.ChartObjects ch.Activate ch.Copy

However, when I do that it copies the chart as a picture instead of a chart.  xlsheet is the new sheet for the copied chart.  I have 4 charts per sheet.

Code:

            For Each ch In xlChart.ChartObjects
                ch.Activate
                ch.Copy
                xlsheet.Range(ch.TopLeftCell.Address).PasteSpecial (xlPasteAll)

Answer 1

Try this:

    For Each ch In xlChart.ChartObjects

        ch.Copy

        xlSheet.Paste Destination:=xlSheet.Range(ch.TopLeftCell.Address)

    Next ch

Answer 2

Perfect!  Thanks!