Getting Error 1004, can't get it to stop

Private Sub Worksheet_Change(ByVal Target As Range)
Application.DisplayAlerts = False
    Dim wsNew As Worksheet, s As Worksheet
    If Target.Cells.Count > 1 Then Exit Sub

   On Error Resume Next
 
    If Not Intersect(Target, Range("A2:A600")) Is Nothing Then
        'Set wsNew = Sheets(Target.Offset(0, 1).Text & "_" & Target.Offset(0, 2).Text)
        If wsNew Is Nothing Then


    Worksheets("Temp").Copy After:=Worksheets(Worksheets.Count)
    
    
    Worksheets(Worksheets.Count).Name = Target.Offset(0, 1).Text & "_" & Target.Offset(0, 2).Text
 Err.Clear
    
    
'ErrorHandler:
If Err = 1004 Then
   
For Each s In ActiveWorkbook.Sheets
    If Right(s.Name, 5) = "(*" Then
        s.Delete
    End If
Next s
End If
  
    Exit Sub
    End If
    End If
    
End Sub

I want to be able have it only warn me there is already a sheet with that name but not copy the original with the (#). I have about 24 of these now. Temp (#).

You have commented out a line... without that line your macro does not check whether that sheet already exits. So your macro would not detect the condition of duplicate names. If you re-enable the "set wsNew..." line then it should skip over the copy and rename part of the program.
Also if you want a message then just before

If wsNew Is Nothing Then

add a line:

If Not wsNew Is Nothing Then MsgBox ("That sheet already exists")

Hi there,

Ensure this part...

Target.Offset(0, 1).Text & "_" & Target.Offset(0, 2).Text

...of this line of code:

Worksheets(Worksheets.Count).Name = Target.Offset(0, 1).Text & "_" & Target.Offset(0, 2).Text

is not blank or can be a valid sheet name i.e. not greater than 31 characters for example.

You do need some code to check if the proposed tab name already exists in the workbook or not.

Regards,

Robert

I think you have your VB editor set to break on all errors, which is the only reason your On Error Resume Next statement would be ignored.

I always have it set to break in class modules.