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

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

Thanks Bob....however, your code removed the if Target.Address = "$E$9"
then piece...how do I tell it what value to look at to use. It still does
not change the tab names that I need. It seems handle 67 of these code
sections but no more...is there a limit? Thanks.

"Bob Phillips" wrote:

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

I removed that because that is what this line does

If Not Intersect(Target, Range("E9")) Is Nothing Then

it was superfluous.

This code, if inserted where shown, will work on any worksheet in the
workbook, regardless of how many there are.


--
HTH

Bob Phillips

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

"deeds" wrote in message
...
Thanks Bob....however, your code removed the if Target.Address = "$E$9"
then piece...how do I tell it what value to look at to use. It still does
not change the tab names that I need. It seems handle 67 of these code
sections but no more...is there a limit? Thanks.

"Bob Phillips" wrote:

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

Thanks Bob! That worked! Just curious...with the other code I had...is
there a limit? Seems like 67. Thanks again!

"Bob Phillips" wrote:

I removed that because that is what this line does

If Not Intersect(Target, Range("E9")) Is Nothing Then

it was superfluous.

This code, if inserted where shown, will work on any worksheet in the
workbook, regardless of how many there are.


--
HTH

Bob Phillips

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

"deeds" wrote in message
...
Thanks Bob....however, your code removed the if Target.Address = "$E$9"
then piece...how do I tell it what value to look at to use. It still does
not change the tab names that I need. It seems handle 67 of these code
sections but no more...is there a limit? Thanks.

"Bob Phillips" wrote:

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

Not that I am aware of. There may be such a limit, I just don't know of it.

--
HTH

Bob Phillips

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

"deeds" wrote in message
...
Thanks Bob! That worked! Just curious...with the other code I had...is
there a limit? Seems like 67. Thanks again!

"Bob Phillips" wrote:

I removed that because that is what this line does

If Not Intersect(Target, Range("E9")) Is Nothing Then

it was superfluous.

This code, if inserted where shown, will work on any worksheet in the
workbook, regardless of how many there are.


--
HTH

Bob Phillips

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

"deeds" wrote in message
...
Thanks Bob....however, your code removed the if Target.Address =
"$E$9"
then piece...how do I tell it what value to look at to use. It still
does
not change the tab names that I need. It seems handle 67 of these
code
sections but no more...is there a limit? Thanks.

"Bob Phillips" wrote:

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

Thanks again Bob...One more question: When I am on the entry sheet each time
you move to a cell the hourglass comes up and it runs the code...is there a
way to hide this "waiting" so it is transparent to the user? It takes about
5 seconds of waiting each time you move one cell on the entry sheet. Thanks.

"Bob Phillips" wrote:

Not that I am aware of. There may be such a limit, I just don't know of it.

--
HTH

Bob Phillips

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

"deeds" wrote in message
...
Thanks Bob! That worked! Just curious...with the other code I had...is
there a limit? Seems like 67. Thanks again!

"Bob Phillips" wrote:

I removed that because that is what this line does

If Not Intersect(Target, Range("E9")) Is Nothing Then

it was superfluous.

This code, if inserted where shown, will work on any worksheet in the
workbook, regardless of how many there are.


--
HTH

Bob Phillips

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

"deeds" wrote in message
...
Thanks Bob....however, your code removed the if Target.Address =
"$E$9"
then piece...how do I tell it what value to look at to use. It still
does
not change the tab names that I need. It seems handle 67 of these
code
sections but no more...is there a limit? Thanks.

"Bob Phillips" wrote:

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

Really? Have you stepped through the code to see what is happening?

--
HTH

Bob Phillips

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

"deeds" wrote in message
...
Thanks again Bob...One more question: When I am on the entry sheet each
time
you move to a cell the hourglass comes up and it runs the code...is there
a
way to hide this "waiting" so it is transparent to the user? It takes
about
5 seconds of waiting each time you move one cell on the entry sheet.
Thanks.

"Bob Phillips" wrote:

Not that I am aware of. There may be such a limit, I just don't know of
it.

--
HTH

Bob Phillips

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

"deeds" wrote in message
...
Thanks Bob! That worked! Just curious...with the other code I
had...is
there a limit? Seems like 67. Thanks again!

"Bob Phillips" wrote:

I removed that because that is what this line does

If Not Intersect(Target, Range("E9")) Is Nothing Then

it was superfluous.

This code, if inserted where shown, will work on any worksheet in
the
workbook, regardless of how many there are.


--
HTH

Bob Phillips

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

"deeds" wrote in message
...
Thanks Bob....however, your code removed the if Target.Address =
"$E$9"
then piece...how do I tell it what value to look at to use. It
still
does
not change the tab names that I need. It seems handle 67 of these
code
sections but no more...is there a limit? Thanks.

"Bob Phillips" wrote:

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