Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Tab Names (Excel 2003)

    Is there a way to create a macro that will name worksheet tabs, by referencing a specific cell in each worksheet?

    I tried searching the site for this info, but could not locate a reference to this type of request, although I believe I've read posts concerning this idea.
    Thanks for any help you can provide,
    Tom

  2. #2
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Tab Names (Excel 2003)

    Hans,
    Thank you for your help!!!

    Have a great weekend,
    Tom

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

    Re: Tab Names (Excel 2003)

    If you want the sheets to be renamed automatically when the user changes the value of the specific cell, see <post#=662,550>post 662,550</post#> - this code assumes that the specific cell is A1, and it should be copied into the ThisWorkbook module of the workbook.

    If you want a macro that can be run by the user (for example by clicking a button), you can use this:
    <code>
    Sub RenameSheets()
    Dim wsh As Worksheet
    On Error Resume Next
    For Each wsh In Worksheets
    wsh.Name = wsh.Range("A1")
    Next wsh
    End Sub
    </code>
    Change A1 to the cell that contains the name to be used.

  4. #4
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Tab Names (Excel 2003)

    Hans,
    I got the macro to work, however I want to name the tab with a date, and the macro won't put the date format onto the tab. I beleive it has to do with the "/" (06/15/2008) not being accepted as a character in the tab.

    Can you suggest a work-around?

    Thanks,
    Tom

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

    Re: Tab Names (Excel 2003)

    The characters / ? * [ and ] are not allowed in sheet names. You could change the line

    wsh.Name = wsh.Range("A1")

    to

    wsh.Name = Format(wsh.Range("A1"), "yyyymmdd")

    or

    wsh.Name = Format(wsh.Range("A1"), "d-mmm-yyyy")

    or any other allowed format.

  6. #6
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Tab Names (Excel 2003)

    Awesome! The line replacement 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
  •