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!

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

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


---
Message posted from http://www.ExcelForum.com/

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

--
steveb
(Remove 'NOSPAM' from email address if replying direct)


"Matt" wrote in message
...

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!

Sheet1.Name = Sheet1.Cells(5, 1).Value - Piku

--
Message posted from http://www.ExcelForum.com

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

On Wed, 12 May 2004 10:45:34 -0700, "Matt"
wrote:


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!