Thanks Tom...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? Thanks again!

"Tom Ogilvy" wrote:

You would need to use the change event in Home and use the code name for the
sheet to be renamed.

Right click on the Home sheet Tab and select view code, then in the
resulting module, at the left top dropdown, select worksheet and in the right
top dropdown select Change (not selectionchange). It will put code like this
in the module

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

This will fire whenever any cell is edited. You can add code to react to
entries in certain cells

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

sheet3 isn't the tab name of the sheet. If you go into the VBE in the
project explorer you will see each sheet has two names. One is the tab name,
the other is the sheet name.

for example if I had a sheet with a tab name of Dog, in the project explorer
it might be listed as

Sheet2 (Dog)

the Sheet2 is the codename.

--
Regards,
Tom Ogilvy




"deeds" wrote:

I have seen several close solutions...here is what I am trying to do:

Sheet "HOME" has a range from e9:e24 When user enters in cell e9 that entry
is linked to cell c6 on another sheet. I want that sheet to be renamed to
that entry. I used anothers code to have it work, however, I need to edit
cell c6, then hit enter and it will change. I need to have it automatically
change the sheet name. I.E. User is on sheet "HOME", they enter in cell E9
and they see the associated sheet name change. Thanks in advance!