worksheet tabs
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: worksheet tabs

  1. #1
    Board Regular j3andc's Avatar
    Join Date
    Mar 2002
    Location
    Michigan, USA
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Is there a formula in Excel that will display the worksheet tab name when requested?

    Thanks

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Someone out there correct me if this is not the shortest or most efficient formula, but try:

    =RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))

    and be sure to immediately save the workbook.

    BTW, the VBA line of code to do this is

    [A1] = ActiveSheet.Name

    Tom Urtis

  3. #3
    Board Regular j3andc's Avatar
    Join Date
    Mar 2002
    Location
    Michigan, USA
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the response, but I do not believe
    this works with multiple sheets. Every time that it recalculates every sheet takes on the same worksheet tab name. Maybe I have done it incorrectly.

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Try pasting this into your workbook module, which will do the trick for all sheets in the workbook. Right click on the Excel workbook icon to the left of the File menu, near the upper left corner of the screen, then left click on View Code, and paste this in:

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    [A1] = ActiveSheet.Name
    End Sub

    Or, if you only want certain sheets that you decide shall have the sheet name displayed, use this code instead, by right clicking on the specific sheet tab(s), left clicking on View Code, and pasting this in:

    Private Sub Worksheet_Activate()
    [A1] = ActiveSheet.Name
    End Sub

    Tom Urtis

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com