Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re-Index Worksheets (Excel)

    How do you re-index the worksheets. If I have a look at the sheet numbers in VBA it shows Sheet511111111111115 (Template). I would like to show just Sheet5

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Re-Index Worksheets (Excel)

    You can use the following macro. See <post:=584,261>post 584,261</post:> for requirements.

    Sub RenameSheets()
    Dim vbc As VBComponent
    Dim n As Integer
    For Each vbc In ActiveWorkbook.VBProject.VBComponents
    If vbc.Type = vbext_ct_Document Then
    If Not vbc.Name = "ThisWorkbook" Then
    n = n + 1
    vbc.Name = "Sheet" & n
    End If
    End If
    Next vbc
    End Sub

    Try it out on a copy of your workbook.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Re-Index Worksheets (Excel)

    Thanks Hans You've saved me again. Do you by change know why this happens? I've logged the problem on MrExcel yesterday could not get through to WOPR and I've logged your solution there. I've noted it is your solution. I hope you don't mind

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Re-Index Worksheets (Excel)

    There was a problem in Excel 97 - when you made successive copies of a sheet named Sheet3, the codename (VBA name) of the copies would be named Sheet31, Sheet311, Sheet3111 etc., eventually resulting in very long names that cause problems. This was corrected in Excel 2000, but old workbooks could still have the long VBA names.

    See XL97: Copy Method of Sheets Object Causes Invalid Page Fault for some background information.

    (The Lounge was inaccessible for some 24 hours due to a technical problem)

Posting Permissions

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