Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel tab Names (Excel2003)

    Has anyone heard of a way to name excel workbook tabs from a list within the workbook itself? I have an extensive list that also identifies a sheet within the workbook, so it would be great if I could name them identically. Unfortunately, the list is quite large. Here is an example:

    Within a workbook I have 1000 sheets and I want to name each sheet after a changing list of people, like person1, person2, person3,...person1000... etc. I just got 2003 and see you can now change the color, but I do not know any way to change the name other than right click. I tried searching for VBScript examples for ideas and came up empty.

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

    Re: Excel tab Names (Excel2003)

    Welcome to Woody's Lounge!

    A workbook with 1,000 sheets is rather unwieldy. Often, it's better to keep the data in one sheet.

    You can use a macro to rename sheets.
    Let's say that the first sheet in the workbook contains the list of names, in A2:A1000 (or whatever). You want to rename the second, third etc. sheet with these names.
    - Press Alt+F11 to activate the Visual Basic Editor.
    - Select Insert | Module to create a new code module.
    - Copy the following code into the module and adjust as needed.

    Sub RenameSheets
    Dim i As Long
    On Error GoTo ErrHandler
    For i = 2 To ActiveWorkbook.Worksheets.Count
    ActiveWorkbook.Worksheets(i).Name = ActiveWorkbook.Worksheets(1).Range("A" & i)
    Next i
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

    It's up to you to ensure that the list of names has unique entries and that there are entries to match all worksheets. See the thread starting at <post#=360115>post 360115</post#> for similar code, with more elaborate error checking.

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel tab Names (Excel2003)

    That worked great- thanks!

Posting Permissions

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