Hey Tom, let's dig into why your code is misbehaving when there's no duplicate worksheet name. The core problem here is twofold: your error handler catches all errors (not just duplicate names) and there's no check to verify which error actually occurred. Plus, relying solely on error handling for an expected scenario (duplicate sheet names) can lead to unexpected behavior like the one you're seeing.

First, Let's Look At Your Original Code #

On Error GoTo Errmsg
'Some code
Exit Sub
Errmsg:
MsgBox "Worksheet with that name already exists, please edit the test iteration"
Application.DisplayAlerts = False
With ActiveWorkbook
    .Worksheets(.Worksheets.Count).Delete
End With
Application.DisplayAlerts = True
End Sub

Why This Happens #

When you run the code without a duplicate sheet name, if any error pops up in your 'Some code section (like a permission issue, a protected workbook, or even a typo in your sheet name variable), the On Error GoTo Errmsg will trigger your error handler. That's why your newly created sheet gets deleted—your code assumes a duplicate error happened, but it's actually a different, unforeseen issue.

Solution 1: Improve Error Handling to Target Only Duplicate Sheet Errors #

VBA has a specific error code for duplicate worksheet names: 1004. We can modify your error handler to only run the deletion logic when this exact error occurs, and surface other errors so you can debug them:

Sub CreateWorksheet_Fixed()
    On Error GoTo Errmsg
    
    ' Replace this with your actual worksheet creation code
    Dim targetSheetName As String
    targetSheetName = "TestSheet" ' Use your variable here
    Dim newSheet As Worksheet
    
    ' Create the new sheet
    Set newSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    newSheet.Name = targetSheetName ' This line triggers the duplicate error if name exists
    
    Exit Sub ' Exit if no errors occur

Errmsg:
    ' Only handle the duplicate sheet name error (code 1004)
    If Err.Number = 1004 Then
        MsgBox "Worksheet with that name already exists, please edit the test iteration", vbExclamation
        ' Delete the unused blank sheet we just created
        Application.DisplayAlerts = False
        ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Delete
        Application.DisplayAlerts = True
    Else
        ' Show the actual error for debugging other issues
        MsgBox "Unexpected error: " & Err.Description & vbCrLf & "Error Code: " & Err.Number, vbCritical
    End If
    
    ' Reset error handling to default
    On Error GoTo 0
End Sub

Solution 2: Check for Duplicate Sheets First (Recommended) #

Instead of relying on error handling for an expected scenario, it's better to proactively check if the sheet exists before creating it. This makes your code more readable and avoids accidental deletions:

Sub CreateWorksheet_Safe()
    Dim targetSheetName As String
    targetSheetName = "TestSheet" ' Replace with your sheet name variable
    Dim existingSheet As Worksheet
    
    ' Check if the sheet already exists
    On Error Resume Next ' Temporarily suppress errors to check for existence
    Set existingSheet = ThisWorkbook.Worksheets(targetSheetName)
    On Error GoTo 0 ' Restore normal error handling
    
    If Not existingSheet Is Nothing Then
        ' Sheet exists - show message and exit
        MsgBox "Worksheet with that name already exists, please edit the test iteration", vbExclamation
        Exit Sub
    End If
    
    ' Sheet doesn't exist - create and name it
    Dim newSheet As Worksheet
    Set newSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    newSheet.Name = targetSheetName
    
    ' Add any additional logic for your new sheet here
End Sub

Key Takeaways #

• Avoid overusing error handling for expected conditions (like duplicate names). Proactive checks are cleaner and more reliable.
• Always validate error codes when using On Error GoTo—this prevents your handler from running for unrelated issues.
• Use ThisWorkbook instead of ActiveWorkbook to ensure you're modifying the correct workbook (avoids issues if another workbook is active).

内容的提问来源于stack exchange，提问作者Tom