Excel 2003 - VB Code - Run time error....pls help :(

[KnightSurfer]

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.

 

[Tom Ogilvy]

Sounds like the old font scaling problem in xl97. If you aren't using xl97,
then make sure the zoom is set at 100%.

You might explore using a pivot table and creating only one chart. If you
are creating static charts and your users are incapable of mastering
pivottables and pivot graphs, then work with one chart, configure it, then
copy as a picutre and paste it to your location. reconfigure, copy as
picutre and paste, Continue on with the other graphs.

 

[KnightSurfer]

Tom,

Many thanks for the quick reply.

1) The template graph that is being used to copy from is set to 100%
2) i cant issue the graphs as a pivot table, as when the report is run, i
then print off all the graphs to a printer for a 30-40 page report.
3) If i created a graph to a location (in the spreadsheet, by creating
another tab/worksheet.) then copy it as a picture to a new page. wouldnt that
be the same as creating over 39 worksheets ?. even if i delete each tab
after its created, and add more, it still fails after about 39 new tabs.

I told you it was a challenging one....

Many Thanks though for taking the time to rely,
Arty.

 

[Tom Ogilvy]

wouldn't it be the same a creating 39 worksheets - sure, but you aren't
having a problem creating 39 worksheets.

 

[KnightSurfer]

Ok, i will give it a go, hopefully today.

Many Many Thanks again.
Arty