ChartObject.Copy *very* slow

[Chrisso]

Hi All

I have some code where I use a templated chart to make a copy:

Debug.Print " MC 0: " & Now()
Dim chtObjTemplate As ChartObject, wsCharts As Worksheet
Set wsCharts = ThisWorkbook.Sheets("Charts")
Set chtObjTemplate = wsCharts.ChartObjects("Chart 1")
chtObjTemplate.Copy
Debug.Print " MC 1: " & Now()

I am surprised to find that this takes roughly 5 seconds as the debug
statements show:
MC 0: 27/03/08 08:51:45
MC 1: 27/03/08 08:51:50

My code turns off the screen, disables events and sets calculation to
manual:
Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.EnableEvents = False

So does anyone have any idea why this is taking so long and how to
get
around it? This amount of time per chart copy will mean my solution
will not scale.

Is there a better way to ensure that a chart generated automatically
matches the formatting of another chart?

Thanks in advance,
Chrisso

 

[Jon Peltier]

What version of Excel?

I prefer working with chart objects in this context as well, but what if you
use

chtObjTemplate.Chart.Copy

You could copy a properly formatted chart, select your unformatted chart,
and use paste special - formats to apply the formats to the second chart.

- Jon

 

[Chrisso]

Hi Jon

I am running Excel 2002 10.2614

I tried:
chtObjTemplate.Chart.Copy
but this gave an error ("Run time Error 1004 : Method 'Copy' of object
'_Chart' failed")

So I tried:
chtObjTemplate.Chart.ChartArea.Copy
and this was *much* faster and did the same job.

Thanks for you help - much appreciated.

Chrisso

 

[Jon Peltier]

Oh yeah, ChartArea. If I'd actually tried it, I would have posted the
correct code. Glad you figured it out.

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


Hi Jon

I am running Excel 2002 10.2614

I tried:
chtObjTemplate.Chart.Copy
but this gave an error ("Run time Error 1004 : Method 'Copy' of object
'_Chart' failed")

So I tried:
chtObjTemplate.Chart.ChartArea.Copy
and this was *much* faster and did the same job.

Thanks for you help - much appreciated.

Chrisso