CAN THE WORKSHEET OR TAB NAME REFER TO A CELL OR ADDRESS ON THE WORKSHEET?

See

http://www.mcgimpsey.com/excel/event...efromcell.html

In article ,

wrote:

CAN THE WORKSHEET OR TAB NAME REFER TO A CELL OR ADDRESS ON THE WORKSHEET?

THANK YOU VERY MUCH WAS VERY HELPFUL AND WORKED GREAT.

"JE McGimpsey" wrote:

See

http://www.mcgimpsey.com/excel/event...efromcell.html

In article ,

wrote:

CAN THE WORKSHEET OR TAB NAME REFER TO A CELL OR ADDRESS ON THE WORKSHEET?

Can someone modify this code to add a number after the sheet name if it a
duplicate?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A1"
Const sERROR As String = "Invalid worksheet name in cell "
Dim sSheetName As String

With Target
If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then
sSheetName = Range(sNAMECELL).Value
If Not sSheetName = "" Then
On Error Resume Next
Me.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Me.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub

And if I delete say a duplicate sheet (Sheet(1)) and copy another sheet and
can it change the duplicate sheet to Sheet(1) and so on?