Tom’s Tutorials for Excel: 3 Methods to Display Sheet Tab Names in a Cell

Tom’s Tutorials for Excel: 3 methods to display sheet tab name in a cell
“How do you display a sheet tab name in a cell?” is one of the questions Tom Urtis is most frequently asked.
Here you have 3 useful answers

Method 1 – by formula, workbook must be named (saved at least once).

=MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,32)

Method 2 – by UDF (workbook does not need to be named):

Function SheetName() As String
Application.Volatile
SheetName = ActiveSheet.Name
End Function

UDF formula entry:
=SheetName()

Method 3 – Macro (workbook does not need to be named)

Sub SheetName()
ActiveCell.Value = ActiveSheet.Name
End Sub

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,
4 comments on “Tom’s Tutorials for Excel: 3 Methods to Display Sheet Tab Names in a Cell
  1. Ben says:

    Thanks for this very useful post and all the other posts you’ve shared. I’m trying to find a way to do the opposite of this, name a sheet based on a cell. Do you know any ways to accomplish this?

    For example, I run a monthly for 100 different cost centers where the reporting tool generates 1 excel file with 100 sheets, one for each cost center. The sheets use the standard excel naming scheme: sheet 1, sheet 2, sheet 3… My reports are formatted the same way where the cost center number is always located in cell A2. What I’m trying to do is rename each of the individual sheets to the number located in cell A2. (00-100, 00-200, 00-300,…) Do you have any suggestions?

    • Tom Urtis says:

      Thanks for following my blog. If the values in cell A2 follow all sheet naming rules, a macro like this can do what you are asking for:

      Sub NameSheets()
      Application.ScreenUpdating = False
      Dim intSheet As Integer
      For intSheet = 1 To Worksheets.Count
      With Worksheets(intSheet)
      .Name = .Range(“A2”).Value
      End With
      Next intSheet
      Application.ScreenUpdating = True
      End Sub

  2. Ben says:

    Thank you for your quick response. I created a workbook to test out the macro with three sheets: sheet 1, sheet 2, and sheet 3, with “a”, “b”, and “c” respectively in cell A2. When I run the macro I receive this error message on the .Name = .Range(“A2”).Value line: “Run time error ‘1004’: Application-defined or object-defined error”

    Any suggestions?

    • Tom Urtis says:

      I just tested the macro again with the same a, b, and c values in three separate worksheets and the macro ran without any problems as expected.

      Take a close look to make sure you actually put those values in cell A2 of each sheet. If you had values in the A2 cells that were greater than 31 characters, or contained characters such as : or ? or [ then that could also be a reason (obviously that is not the case with just a, b, and c as you say). Otherwise, something else is going on that I cannot tell from here, at least with regards to bona fide legal sheet name values and running the code as I posted it.

Leave a Reply

Your email address will not be published. Required fields are marked *

*