EXCEL 2003 - Run Time Error... want a challenge !

Greetings,

Heres a challenge, i cant find a solution to this anywhere.

In EXCEL.

Ive got VB code to copy a Template of a Chart, and then populate it with data.
It needs to create about 40-45 graphs... i do this every monday so its
automated for that reason.

anyway, if it creates over around 39 graphs, i get this error:
-----
Run-time error '1004'
copy method of Chart class failed.
-----

This is the line of code is stops on.

Sheets("TEMPLATE").Select
Sheets("TEMPLATE").Copy after:=Sheets(Sheets.Count)

Sheets("TEMPLATE (2)").Select
Sheets("TEMPLATE (2)").Name = KPR_Sheet

-------

Any help would be great.

if i save and close the spreadsheet, it seems to reset the error. ie if i
restart it after i have opened it, i can get another 40 odd sheets before it
fails.

Hi KnightSurfer,

> Ive got VB code to copy a Template of a Chart, and then populate it with data.
> It needs to create about 40-45 graphs... i do this every monday so its
> automated for that reason.
>
I'm afraid you've hit a problem with Excel here. Copying sheets with charts on
them (or chart sheets) has a memory leak on certain Excel versions, which indeed
can only be solved by restarting Excel.

Since you seem to be copying the same chart over and over, why not create that
chart based on dynamic data. Instead of changing to a different chart sheet, let
the user change the source data using e.g. dropdown boxes or spinner controls.
See my Autochrt.zip for a simple example. Find it on this page:

http://www/jkp-ads.com/Download.htm

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Not sure what the cause is; maybe Excel is not releasing some
resources. If you don't need the charts to be live, you could make one
chart and then copy a picture of it to another sheet and then reuse the
same chart with the next data. I have done similar things in the past
using pictures of charts and have not experienced a problem.


Bill Manville
MVP - Microsoft Excel, Oxford, England