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
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?
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
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?
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.
Hi there tried method 1 with merged cells and it just shows the function no sheet 1
No surprise. Merged cells always cause problems like that. Unmerge your cells and format using Center Across Selection instead.
https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-using-center-across-selection-instead-of-merging-cells/