perhaps this

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If Target.Count 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Not Intersect(Target, Range("E9")) Is Nothing Then
Sh.Name = Target.Value
Sh.Range("C6") = Target.Value
End If

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"deeds" wrote in message
...
Tom provided the code below...the code works great. My next problem: I
have
quite a few tabs to rename. I got to my limit I think. It won't work for
sheets past a certain number of code lines. My question is: Is there a
limit
to the number
of iterations? If so, how do I get around this? Any help would be
appreciated...Thanks!


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.count 1 then exit sub
If Target.Value = "" then exit sub
if not intersect(Target,range("E9:E24")) is nothing then
if Target.Address = "$E$9" then
Sheet3.Name = Target.Value
Sheet3.Range("C6") = Target.Value
end if
' add code for other cells
End if
End Sub