You could use the Code name of the sheet. This only gets changed from VB
Editor (or in code) - it is not the name on the sheet tab (this code only
changes the name on the tab). Just check the VB Editor to get the correct
code name.

But now you have reached my ceiling. I know that ThisWorkbook refers to the
workbook containing the code. Not sure what the eqivalent is for ThisSheet
(not found).

Maybe Frank can give us a clue of how to deal with this when you copy the
worksheet.

''''''''''''''''''''
Private Sub Worksheet_Calculate()
Application.EnableEvents = False

If Sheet2.Range("K5") < 0 Then
If Sheet2.Name < Sheet2.Range("K5") Then
Sheet2.Name = Range("K5")
End If
End If

Application.EnableEvents = True
End Sub
'''''''''''''''''''''''''''''

hth
--

steveB

(Remove 'NOSPAM' from email address if contacting me direct)


"nuver " wrote in message
...
Thank you Frank but I could not get the code to work. I pasted your code
in the Worsheet module but it does not change the name of the
worksheet.

Steve I pasted your code and it did work for the 3rd sheet in the
workbook so I simply changed the index number to 2 and it worked on the
sheet I was trying to change. If I make a copy of the sheet then I need
to go into the code and manually change the index, is there a way to
make this automatic when I creat a copy of the worksheet?

Thank you both for your advice and quick response.


---
Message posted from http://www.ExcelForum.com/