Hi, I am looking for a code that can change the sheet tab
name to correspond with a name in a given cell on the
same worksheet
Many thanks in advance
John

Hi John,

VBA event code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address(False, False) = "A1" Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JohnUK" wrote in message
...
Hi, I am looking for a code that can change the sheet tab
name to correspond with a name in a given cell on the
same worksheet
Many thanks in advance
John

Many thanks Bob
-----Original Message-----
Hi John,

VBA event code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address(False, False) = "A1" Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs
to be
'placed in the appropriate worksheet code module, not a
standard
'code module. To do this, right-click on the sheet tab,
select
'the View Code option from the menu, and paste the code
in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JohnUK" wrote in
message
...
Hi, I am looking for a code that can change the sheet
tab
name to correspond with a name in a given cell on the
same worksheet
Many thanks in advance
John


.

Here's a function approach:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)



"JohnUK" wrote in message
...
Hi, I am looking for a code that can change the sheet tab
name to correspond with a name in a given cell on the
same worksheet
Many thanks in advance
John

This also works:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))


The workbook has to be saved before this actualy works

"Jim May" wrote:

Here's a function approach:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)



"JohnUK" wrote in message
...
Hi, I am looking for a code that can change the sheet tab
name to correspond with a name in a given cell on the
same worksheet
Many thanks in advance
John

Thanks to Bob Phillips and Jim May for their answers to
my last Post

I now need to go a step further - Once the names of the
tabs have been changed, I need a code that can reset the
tab names back to how they were. The problem I have is
that when I run the macro to change the names back, the
macro now doesn't recognise the new names and comes up
with an error message.

I guess it just needs a code that can jump to the next
tab regardless of what it's called - changes it - then
jumps to the next tab and so on.

The workbook has 40 sheets/Tabs, but I only need the
second half (20 sheets) to do its job

Thanks
John

-----Original Message-----
Hi John,

VBA event code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address(False, False) = "A1" Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs
to be
'placed in the appropriate worksheet code module, not a
standard
'code module. To do this, right-click on the sheet tab,
select
'the View Code option from the menu, and paste the code
in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JohnUK" wrote in
message
...
Hi, I am looking for a code that can change the sheet
tab
name to correspond with a name in a given cell on the
same worksheet
Many thanks in advance
John


.

John,

That last bit confused me.

Do you want to preserve the previous value to re-instate it at some time (if
so, what would trigger that re-instating)?

Or are you saying that if you change Sheet1 to say Bob, and then try to
change again to something else, it fails? If so, in what way?

Or is it something else?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JohnUK" wrote in message
...
Thanks to Bob Phillips and Jim May for their answers to
my last Post

I now need to go a step further - Once the names of the
tabs have been changed, I need a code that can reset the
tab names back to how they were. The problem I have is
that when I run the macro to change the names back, the
macro now doesn't recognise the new names and comes up
with an error message.

I guess it just needs a code that can jump to the next
tab regardless of what it's called - changes it - then
jumps to the next tab and so on.

The workbook has 40 sheets/Tabs, but I only need the
second half (20 sheets) to do its job

Thanks
John

-----Original Message-----
Hi John,

VBA event code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address(False, False) = "A1" Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs
to be
'placed in the appropriate worksheet code module, not a
standard
'code module. To do this, right-click on the sheet tab,
select
'the View Code option from the menu, and paste the code
in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JohnUK" wrote in
message
...
Hi, I am looking for a code that can change the sheet
tab
name to correspond with a name in a given cell on the
same worksheet
Many thanks in advance
John


.

Sorry Bob, I must admit that I am not that good at
explaining, but here is another go-

Yes I would need the tabs to change back to there
previous names, which were 20, 21, 22, and so forth to 40
as the book closes down, ready to be used again the next
time it opens.

The workbook will be used for importing the data into
AutoRoute, and that is why I needed the tabs names to
change to make it easier to see what sheets / data are
being imported.
Once the workbook is complete, it would be used by a
dozen other people, and rather the risk of them saving
changes, it would automatically reset on close down, and
of course add a button in case any mistakes are made
whilst open.

John


John,

That last bit confused me.

Do you want to preserve the previous value to re-instate
it at some time (if
so, what would trigger that re-instating)?

Or are you saying that if you change Sheet1 to say Bob,
and then try to
change again to something else, it fails? If so, in what
way?

Or is it something else?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JohnUK" wrote in
message
...
Thanks to Bob Phillips and Jim May for their answers to
my last Post

I now need to go a step further - Once the names of
the
tabs have been changed, I need a code that can reset
the
tab names back to how they were. The problem I have
is
that when I run the macro to change the names back,
the
macro now doesn't recognise the new names and comes
up
with an error message.

I guess it just needs a code that can jump to the
next
tab regardless of what it's called - changes it -
then
jumps to the next tab and so on.

The workbook has 40 sheets/Tabs, but I only need the
second half (20 sheets) to do its job

Thanks
John

-----Original Message-----
Hi John,

VBA event code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address(False, False) = "A1" Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it
needs
to be
'placed in the appropriate worksheet code module, not
a
standard
'code module. To do this, right-click on the sheet
tab,
select
'the View Code option from the menu, and paste the
code
in.


--

HTH

RP
(remove nothere from the email address if mailing
direct)


"JohnUK" wrote
in
message
...
Hi, I am looking for a code that can change the
sheet
tab
name to correspond with a name in a given cell on
the
same worksheet
Many thanks in advance
John


.



.