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


.