Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)

on error resume next
sh.Name = sh.Range("a2").Value
if err.number < 0 then
msgbox "Rename failed"
err.clear
end if
on error goto 0
End Sub

Let's Say you don't want it to give you an error if you have two tabs with
same name but you want it to go ahead and rename the tab with the same name
and insert (1),(2),..and so on after
the tab name.

How would you do this

Thanks