Copy Worksheet Causes Crash

[bsmith]

When copying a worksheet within a workbook by right click-
copy/move excel 2002 crashes every time. We are at
office xp sp2 with all hotfixes applied. Has anyone had
this problem/or have a resolution?

 

[Jan Karel Pieterse]

Hi,

This implies that the workbook contains sheets with
illegal code names.
Probably the offending sheet is the copy of the copy of
the copy of .... a sheet and the workbook has originaly
been created in XL97.

These illegal names stem from a bug in XL 97 and surface
when you copy a sheet. XL97 adds a 1 to the code name
(that is NOT the name you see on the sheet's tab) of the
copy, so it's name becomes Sheet11. Copying that sheet
gives you Sheet111, etcetera.

Remedy:

1. Open the file in Excel 97
2. Open the VBE (Alt-F11)
3. Find the file's project in the project explorer
4. Expand the project
5. Make sure the properties window is visible (view menu)
6. Click a sheet
7. Check out the (Name) property, if it looks like the one
stated, change it to something shorter (and unique!)
8. Repeat step 6 for all sheets
9. Close the VBE
10.Save the workbook

In future, use the SAME sheet over and over to create the
copies.

Regards,

Jan Karel Pieterse
Excel TA/MVP

 

[benjy]

Jan,

Thanks for the help but that does not seem to be the
problem. Upon looking at the name in the properties
window there is no expected name problem (ie..testsheet1)
so I am not sure where to go from here but just for fun I
went in as you instructed and updated each worksheet name
in the workbook and I still have the same problem. Just
wanted to say thanks and if you have any other ideas
please let me know.

Benjy

 

[Jan Karel Pieterse]

Hi benjy

Thanks for the help but that does not seem to be the
problem. Upon looking at the name in the properties
window there is no expected name problem (ie..testsheet1)
so I am not sure where to go from here but just for fun I
went in as you instructed and updated each worksheet name
in the workbook and I still have the same problem. Just
wanted to say thanks and if you have any other ideas
please let me know.

Maybe a file corruption then.

- Save the file as filetype webpage (html)
- close and reopen the html file
- save-as normal excel workbook.

Regards,

Jan Karel Pieterse
Excel TA/MVP