Name sheets from a list
 

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!

Name sheets from a list
 

??
activesheet.name=range("e9")

--
Don Guillett
SalesAid Software

"deeds" wrote in message
...
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!

Name sheets from a list
 

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!

Name sheets from a list
 

Thanks Tom! It works! Sorry to keep going (I thought I could modify to my
needs)...I need other sheets named from the range F9:F24. If I add this code
with the new range names..nothing. What do I need to add differently. Also,
what if I wanted to pull from just the "HOME" page...not from the linked cell
on each tab. In other words what do I need to get rid of to just pull from
the "HOME" page. 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!

Name sheets from a list
 

The code as written, placed in the sheet module for the HOME sheet would only
pull from the HOME sheet as I understand the use of the term PULL.

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("F9:F24")) is nothing then
Select Case Target.Address(0,0)
Case "F9"
Sheet3.Name = Target.Value
Sheet3.Range("C6") = Target.Value
Case "F10"
sheet4.Name = Target.Value
sheet4.Range("C6").Value = Target.value
Case "F11"
sheet5.Name = Target.Value
sheet5.Range("C6").Value = Target.Value
' add code for other cells
end select

End Sub

--
Regards,
Tom Ogilvy

"deeds" wrote:

Thanks Tom! It works! Sorry to keep going (I thought I could modify to my
needs)...I need other sheets named from the range F9:F24. If I add this code
with the new range names..nothing. What do I need to add differently. Also,
what if I wanted to pull from just the "HOME" page...not from the linked cell
on each tab. In other words what do I need to get rid of to just pull from
the "HOME" page. 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!

Name sheets from a list
 

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!