How do I automatically rename a sheet with the contents of a cell.

[Norman Jones]

Hi Michael,

Right-Click the worksheet tab, select the 'View Code' option and paste the
following code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Cleanup:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Target
Me.Name = .Value
End With
End If
Cleanup:
Application.EnableEvents = True
End Sub

Change A1 to the required cell reference,

 

[JE McGimpsey]

There's a minor gotcha in this code. By using Me.Name = Target.Value,
rather than the cell reference, an error will occur if multiple cells
are selected (since Name can't be assigned an array).

A variation of this, with some additional error checking, can be seen at

http://www.mcgimpsey.com/excel/events/sheetnamefromcell.html

 

[Norman Jones]

Hi JE,

You are correct and, in any case, I prefer your version.

Thank you.