Worksheet name

[Tony S]

Appreciate any help / advice.

We have a worksheet created in Excel (Office 97) that is
used for a type of stock control. Each sheet refers to a
week and at the end of the week, a sheet is created by
copying the last sheet.

We've found that this now causes Excel to crash when we
try to save. I've looked in the "Visual Basic Editor"
(under "Tools", "Macro") and found that the sheets are
being given a name such as Sheet2111(week4) and then each
new sheet the number increases by an additional digit (eg.
the next sheets would be Sheet21111(week5) and Sheet211111
(week6). (Currently up to Week28!)

Is it possible to modify these names in the VBA editor to
stop the crashing?

Many thanks in advance for any assistance

 

[Norman Jones]

Hi Tony,

This is a known bug in xl97.

To change the sheet name, in the VBE select the offending sheet; your F4
function key will open the Properties window and you can replace the first
property (Name) with a shorter unique alternative.

To prevent repetition of the problem, avoid copying the copy of the copy,
but instead copy the same sheet each week.

 

[Tom Ogilvy]

Just to add, copying the same sheet will delay the onset of the problem. It
will not necessarily prevent it, but the 52 copies shouldn't be a problem
depending on the name of the single sheet.

 

[Guest]

Norman,

many thanks, that's exactly what I needed. I've checked
and it works as per your instructions. Also appreciate the
input from Tom.

Regards

Tony