TAB NAME IN A WOORKBOOK REFER TO A CELL ON WORKSHEET

[Guest]

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

 

[JE McGimpsey]

See

http://www.mcgimpsey.com/excel/events/sheetnamefromcell.html

 

[Guest]

THANK YOU VERY MUCH WAS VERY HELPFUL AND WORKED GREAT.

 

[Han]

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?