Handle error when naming a sheet the same as an existing sheet

Question

ThisWorkbook.Sheets("Project Template").Copy After:=Sheets(Sheets.Count)
Set ws = ActiveSheet

Naming = CallLaunched.Value & " - " & ProjectLead.Value

ws.Name = Naming

It is possible that 'Naming' could be the same as a previously generated sheet and therefore throws up an error. What would be the best way to handle this?

Would it be best to just use an Error Goto? If so would the handler only apply to the Naming line?

Test if the worksheet of that name exist first, check this [answer](https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists) — Raymond Wu, Nov 19 '21 at 08:43
What should happen if a sheet already exists with the required name? — Tim Williams, Nov 19 '21 at 18:07

score 0 · Answer 1 · answered Nov 19 '21 at 13:12

Copy a Worksheet (New Name Issue)

Make sure that Naming contains a valid sheet name.

Option Explicit

Sub CopyWorksheet()
    
    ' Workbook
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    ' Source worksheet
    Dim sws As Worksheet: Set sws = wb.Worksheets("Project Template")
    
    ' Destination Worksheet Name
    Dim Naming As String: Naming = "Launched Project"
    'Naming = CallLaunched.Value & " - " & ProjectLead.Value
    
    ' Attempt to create a reference to the Destination Worksheet.
    On Error Resume Next
    Dim dws As Worksheet: Set dws = wb.Worksheets(Naming)
    On Error GoTo 0
    
    ' If it exists, delete it.
    If Not dws Is Nothing Then ' destination exists
        Application.DisplayAlerts = False ' delete without confirmation
        dws.Delete
        Application.DisplayAlerts = True
    Else ' destination doesn't exist
    End If
    
    ' Only now create a copy,...
    sws.Copy After:=wb.Sheets(wb.Sheets.Count)
    ' ... a reference,...
    Set dws = ActiveSheet
    ' and rename it.
    dws.Name = Naming
    
End Sub

score 0 · Answer 2 · answered Nov 19 '21 at 13:41

When manually copying a sheet the new name is numbered to make it unique, for example "Sheet1 (2)". If you want to allow copying a sheet multiple times you could do similar -

On Error Resume Next
ws.Name = Naming
If Err.Number Then
    n = 1
    Do
        Err.Clear
        n = n + 1
        ws.Name = Naming & "  (" & n & ")"
    Loop Until Err.Number = 0 ' Or n <= 50  ' limit how many times can copy?
End If
On error Goto 0

You might need more handling to ensure the new name length does not exceed 31

Handle error when naming a sheet the same as an existing sheet

2 Answers2

Copy a Worksheet (New Name Issue)