worksheet tabs

[j3andc]

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

Thanks

 

[Tom Urtis]

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

 

[j3andc]

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.

 

[Tom Urtis]

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