Name a worksheet tab based on the value in a cell.

[Christine]

Hello all,

I have found a lot of posts about how to populate a cell with the value in
the worksheet tab name, but not the other way around. I would like to
automatically name the worksheet tab based on the value in a specific cell.

I'm ok with formula's but not "up" on VB. Is there a formula I could use to
accomplish this?

Thanks in advance for any help you can give me.

Christine

 

[Paul]

Hi Christine,

A formula cannot name a worksheet, VBA would be required. Fo
example:


Code:
--------------------


Sub nameSheet()
ActiveSheet.Name = ActiveSheet.Range("A1").Value
End Sub
--------------------



This would name the activesheet the same as the value in cell A1. Jus
remember that sheet names can't contain various characters. If A1 i
just text or numbers, no issues.

 

[Roger Govier]

Hi Christine

These 2 subs will do the task.
The first will just name the Active Sheet.
The second will run through the file and name all sheets according to
the value in cell A1 - provided A1 contains a value.

Sub NameSheet()
If Range("A1") <> "" Then
ActiveSheet.Name = Range("A1").Value
End If

ActiveSheet.Name = Range("A1").Value

End Sub

Sub NameSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If ws.Range("A1") <> "" Then
ws.Name = ws.Range("A1").Value
End If
Next ws
End Sub

To install
Copy code as above
Alt+F11 to invoke the Vb Editor
Alt+I+M to Insert a new module
Paste the code into the white pane that appears
Alt+F11 to return to Excel

To Use
Alt+F8
Select the macro to use
Run