Edit this macro so it checks if sheet name already exists

[tonywatsonhelp]

Hi Everyone,
I have macro that works great but every now and then a sheet name is chosen that is already in use.

Is it posible to add i a part that checks if the names in use and if so adds a number at the end
so if sheet name was "Tony" it would do "Tony 2"
starting at 2 so it shows its the second one?

heres my code:

Sub Copy_Sheet()
Application.EnableEvents = False
Sheets("Template").Copy After:=ActiveSheet

ActiveSheet.Name = ActiveSheet.Range("G7")

Application.EnableEvents = True
End Sub

 

[Dossfm0q]

Hi what about below, Feedback PLZ

 On Error Resume Next
    Set NewShe = ThisWorkbook.Sheets(Nm)
    On Error GoTo 0
    If ActiveSheet.Range("G7").Value = "" Then MsgBox "Range(""G7"")  is  Blank"
    If NewShe Is Nothing Then
        ActiveSheet.Name = ActiveSheet.Range("G7")
    Else
        For Each Sht In ThisWorkbook.Sheets
            If InStr(1, Sht.Name, Nm) <> 0 Then
                N = N + 1
            End If
        Next
        ActiveSheet.Name = ActiveSheet.Range("G7") & N
    End If
    
Application.EnableEvents = True

End Sub

 

[tonywatsonhelp]

Thanks DossfmOq,
I csan use this,
thanks
for your help
Tony