If SheetExist("Накладная") Then
..что-то делаю
End if

Function SheetExist(iName As String) As Boolean
    On Error Resume Next
    With Worksheets(iName): End With
    SheetExist = (Err = 0)
    Err.Clear
End Function

Sub test()
Dim Rng As Range, iCell As Variant
    
    Set Rng = Range("A1:A10") 'диапазон с фамилиями
    
    For Each iCell In Rng
        If Len(iCell) > 0 Then
            If Not SheetExist(iCell.Value) Then
                Worksheets.Add After:=Worksheets(Worksheets.Count)
                ActiveSheet.Name = iCell.Value
            End If
        End If
    Next iCell
    
    MsgBox "Листы созданы!", vbInformation, "Конец"
End Sub
 
Function SheetExist(iName As String) As Boolean
    On Error Resume Next
    With Worksheets(iName): End With
    SheetExist = (Err = 0)
    Err.Clear
End Function

Private Sub CBmultilistcreate_Click()
    Dim i As String, diapaz As Range, iCell As Variant, y As Long, _
    j As String, o As String, nStr As Long
    i = ThisWorkbook.Name
    Me.Hide
    Set diapaz = Application.InputBox("Выбирите ячейки!", Type:=8)
    For y = 1 To diapaz.Count
        For Each iCell In diapaz
            If Len(iCell) > 0 Then
                If Not SheetExist(iCell.Value, "рабочие листы.xlsx") Then
                    nStr = Mid(diapaz(y).Address, (InStr(2, diapaz(y).Address, "$") + 1))
                    j = ThisWorkbook.Sheets("Лист1").Cells(nStr, 4).Value & ThisWorkbook.Sheets("Лист1").Cells(nStr, 3).Value
                    o = ThisWorkbook.Sheets("Лист1").Cells(nStr, 7).Value
                    Call CreateList(diapaz(y), j, o)
                End If
            End If
        Next iCell
    Next
    Me.Show
End Sub

Sub CreateList(iName As String, j As String, o As String)
    Windows("рабочие листы.xlsx").Activate
    Sheets("образец листа").Select
    ActiveSheet.Copy after:=ActiveSheet
    ActiveSheet.Name = iName
    Cells(2, 3).Value = iName
    Cells(3, 3).Value = j
    Cells(4, 3).Value = o
End Sub

Function SheetExist(iName As String, iBook As String) As Boolean
    On Error Resume Next
    With Workbooks(iBook).Worksheets(iName): End With
    SheetExist = (Err = 0)
    Err.Clear
End Function