Macro problem with message 400

[Bill Richardson]

I have run into an error on a workbook I created in Excel 97 and has run
with no problems before. The workbook is for planning and tracking
training programs for athletes. It has a yearly summary sheet and a
model sheet for a weeks activities. The macro copies the model sheet 51
times for the remaining weeks of the year and then copies information
from the yearly summary into each week sheet.

When I run the macro it creates weeks up to 43 then halts with an error
box with just the number 400, nothing else. If I delete the new sheets
and try to run the macro again it halts immediately with the same
message. If I exit out of Excel and go back in the same process repeats
itself.

Does anyone have any ideas about the cause of the problem and possible
solutions.

TIA

--
Bill

************************************************************
Bill Richardson
Systems Manager
Sport Technology Research Centre
Faculty of Kinesiology
The University of Calgary
2500 University Dr. N.W.
Calgary, AB
Canada T2N 1N4
Phone: (403) 220-5194
Fax: (403) 284-2098
Internet:[email protected]

 

[Nick Hodge]

Bill

You will find some help in this KB article

http://support.microsoft.com/default.aspx?scid=kb;en-us;177634&Product=xlw

Basically, behind the scenes XL issues a CodeName to its sheets. When you
copy a sheet that starts with a codename of Sheet1 a first copy is Sheet11
and the next Sheet111. You can only get to a length of about 35 characters,
so I suspect this is the issue. There is a workaround in the KB article

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)