Change sheet name

M

Matt

I'm trying to write code that will rename a worksheet with
the value in a cell. ex: cell a5 (sheet1) value is
NewName. I'd like to grab that text and change the name
of sheet1 to NewName. Any suggestions? Thanks in advance!
 
P

pikus

Worksheets(1).Name = Worksheets(1).Cells(5, 1).Value

This will work, but I'll post an improvement in a bit.
- Pikus
 
S

steveb

Matt,

This worked for me in Excel 2002

ActiveSheet.Name = Range("A5")

but errors out if the cell is blank - so add something to catch the error.

hth
 
G

Gord Dibben

Matt

From Bob Phillips..........

Private Sub Worksheet_Change(ByVal Target As Range)
'autoname the worksheet Tab from value in A5
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A5")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value <> "" Then
Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Alternate on a button or shortcut key.

Sub SheetName()
ActiveSheet.Name = Range("a5")
End Sub

Gord Dibben Excel MVP
 
Top