VBA to insert and name sheet but avoid duplicate sheet name

[General Ledger]

Dear All,

I have VBA to add a new sheet.

    ActiveSheet.Copy Before:=Sheets(1)
    ActiveSheet.NAME = "Entry"

However, I get an error if that sheet name already exits.

Run-time error '1004':
Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic.

How can I insert a new sheet and name it without creating a duplicate?

Concatenating a sequence number to the end of the sheet name if the desired name already exits is fine. This would create sheets with names: Entry, Entry 1, Entry 2, etc.

Thanks,

GL

 

[VoG]

Try like this

Sub test()
Dim i As Long, s As String, s2 As String
s = "Entry"
s2 = s
ActiveSheet.Copy Before:=Sheets(1)
Do While WorksheetExists(s2)
    i = i + 1
    s2 = s & " " & i
Loop
ActiveSheet.Name = s2
End Sub


Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

 

[General Ledger]

VoG,

Wow, this is way over my head. Can you consolidate this to one macro instead of a macro and a function?

Thanks,

GL