Excel crashes when copying sheet (97 sp2)

Topic

New Reply

I copy a whole worksheet then rename the tab. After doing this several times Excel crashes.

In the VBA IDE, the class name for the sheet is Sheet22. After the first copy it is Sheet221, the second copy – Sheet2211, third – Sheet22111, fourth Sheet221111, Etc. until the name is too long. Then I crash. I cannot simply rename the class Sheet221111111111111111111111111 to ‘OtherName’. When I try I simply crash. The only workaround I have found is to copy the contents of all the worksheets to a new workbook. Actually I can copy all but the last worksheet to a new workbook, then copy the contents of the last one.

Is there a fix or a better workaround?

Reply | Quote

Replies

This has been a problem with earlier versions of Excel, up to 97 if not 2000.

You could try copying the same sheet each time rather than a copy of the sheet, that is swap the copied sheet twith the original if possible.

Or you could insert a new sheet and copy the data from the source sheet.

Or you can rename the code name of the sheet either in the VBA IDE or in the standard Excel environment by selecting the Properties Icon on the Controls Toolbox. Just change the (Name) property. See attached screenshot.

Andrew C

Reply | Quote

Thanks. However, renaming the class does not work. By the time the name length limit is reached, Excel crashes even when I rename the last class. [What you are calling it a code name I call a class. IMHO it is a class. Perhaps not a true class as Java would provide, but I can create properties and refer to the properties by the class name).

The issue here is the end users. They come to me when it crashes. I could tell them, “I have put dynamite under your desk. Each time you copy a worksheet, follow these renaming steps. If you do not, when Excel crashes, the dynamite will blow up” However, the little remaining little parts of them will still come to my desk on that fateful day crying “My report is due. I don’t care how important your work is, please drop it and bail my butt out!” Since the end user is generally a manager higher than I, I comply.

I was hoping someone could pulled a rabbit out of a hat and provide a final solution. Alas, I will live with it and continue my love/hate relationship with Microsoft.

Thanks.

Reply | Quote

But this crash only happens when users copy the copy of the copy of …..
Teach them to copy the first sheet every time and they should be off your case .

Alternatively you could give them a button that does the copying for them, avoiding this problem.:

Sub CopyTidy()
Dim oSheet As Worksheet
Dim oActivesheet As Worksheet
Dim sName As String
Set oActivesheet = ActiveWorkbook.ActiveSheet
Set oSheet = ActiveWorkbook.Worksheets.Add(, oActivesheet)
oActivesheet.Columns.Copy oSheet.[a1]
sName = InputBox(“Enter a name for the new sheet”, “Copy current sheet”, oSheet.Name)
If sName = “” Then Exit Sub
oSheet.Name = Left(sName, 31)
End Sub

Reply | Quote

Looks like you are running up against Excel internal limits on Worksheet Code Names (should be some related threads in the Forum, but I can’t find them). See if the thread starting at post 98273 and especially Sammy’s reference to Chip Pearson on Code Names which is almost

Reply | Quote

This is an old thread, but probably still an occasional issue for some. I have a workbook that would not let me copy or insert a new sheet, but always crashed due to the worksheet code names being too long. Neither could I rename the sheet code names in VBA as anything I renamed it to was reported as invalid.

I fixed the problem by saving the wookbood in 5.0/95 format…it renamed all the sheet code names for me. There was of course a warning that some formatting would be lost, but that was a trivial problem in this case.

Reply | Quote

This is an old thread, but probably still an occasional issue for some. I have a workbook that would not let me copy or insert a new sheet, but always crashed due to the worksheet code names being too long. Neither could I rename the sheet code names in VBA as anything I renamed it to was reported as invalid.

I fixed the problem by saving the wookbood in 5.0/95 format…it renamed all the sheet code names for me. There was of course a warning that some formatting would be lost, but that was a trivial problem in this case.

Reply | Quote