desperately put validation if statement in VBA

[Flicker]

Hi sorry about this question. It seems very easy but I've spent several hour to bang my head and I can't figure it out.

I want to insert multiple worksheet with VBA by refer range of cell and use text from those range as a sheet name. I did search and found VBA code that I can use.

However, I want to add a validation -- if the sheet name is exist, do not add any sheet. just skip it but I can't insert the simple IF statement in VBA due to lack of VBA knowledge. Moreover, I try to move On Error Resume Next above Sheets.Add and hope On Error GoTo 0 would negate the Sheets.Add step. However, VBA still create sheet with default name (e.g. Sheet15) which is not what I expect.

Sub AddSheets()
'Updateby Extendoffice 20161215
    Dim xRg As Excel.Range
    Dim wSh As Excel.Worksheet
    Dim wBk As Excel.Workbook
    Set wSh = ActiveSheet
    Set wBk = ActiveWorkbook
    Application.ScreenUpdating = False
    For Each xRg In wSh.Range("E2:E9")
        With wBk
        'if the sheet with that name exist, should not add sheet.
        
        
        'else add sheet.
        
            .Sheets.Add after:=.Sheets(.Sheets.Count)
            On Error Resume Next
            ActiveSheet.Name = xRg.Value
            If Err.Number = 1004 Then
              Debug.Print xRg.Value & " already used as a sheet name"
            End If
            On Error GoTo 0
        End With
    Next xRg
    Application.ScreenUpdating = True
End Sub

could someone point me what I misunderstand here?

Thank you very much

 

[Flicker]

It's seems working now. but any suggestions or improvements always welcome.

Sub AddSheets()
'Updateby Extendoffice 20161215
    Dim xRg As Excel.Range
    Dim wSh As Excel.Worksheet
    Dim wBk As Excel.Workbook
    
    Set wSh = ActiveSheet
    Set wBk = ActiveWorkbook
    Application.ScreenUpdating = False
    
    For Each xRg In wSh.Range("E2:E9")
        
        For i = 1 To Worksheets.Count
            If Worksheets(i).Name = xRg.Value Then
                exists = True
            End If
        Next i
            
        If Not exists Then
            
            With wBk
                .Sheets.Add after:=.Sheets(.Sheets.Count)
                On Error Resume Next
                ActiveSheet.Name = xRg.Value
                If Err.Number = 1004 Then
                  Debug.Print xRg.Value & " already used as a sheet name"
                End If
                On Error GoTo 0
            End With
            
        End If
            
    exists = False
    Next xRg
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

This assumes that the values in E are valid sheet names

Sub Flicker()
   Dim Cl As Range
   
   For Each Cl In Range("E2:E9")
      If Cl <> "" Then
         If Not Evaluate("isref('" & Cl.Value & "'!A1)") Then
            Sheets.Add(, Sheets(Sheets.Count)).Name = Cl.Value
         End If
      End If
   Next Cl
End Sub