Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel crashes when copying sheet (97 sp2)

    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?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel crashes when copying sheet (97 sp2)

    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
    Attached Images Attached Images

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel crashes when copying sheet (97 sp2)

    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>post 98273</post#> and especially Sammy's reference to Chip Pearson on Code Names which is almost
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel crashes when copying sheet (97 sp2)

    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.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel crashes when copying sheet (97 sp2)

    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 <g>.

    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
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel crashes when copying sheet (97 sp2)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •