Sub test1()
    Dim SheetName As String, sh
    On Error Resume Next
    Set sh = ActiveWorkbook.Sheets(SheetName)
    On Error GoTo  0 
    If sh Is Nothing Then MsgBox "лист не существует" Else MsgBox "лист существует"
    'В дальнейшем используем переменную sh, напр.: sh.Name="Архив"
End Sub

Sub test2()
    Dim SheetName As String
    On Error Resume Next: Application.DisplayAlerts = False
    ActiveWorkbook.Sheets(SheetName).Delete
    Application.DisplayAlerts = True: On Error GoTo  0 
    'Остальной код
End Sub

    For i = BeginRowListCFO To EndRowListCFO
        If Cells(i, BeginColumnListCFO).Value =  1  Then
            SheetCFO = Cells(i, ColumnSheetName).Value
            On Error Resume Next
            Set Sh = ActiveWorkbook.Sheets(SheetCFO)
            On Error GoTo  0 
            If Sh Is Nothing Then MsgBox "Не существует" Else MsgBox "Существует"
            Set Sh = Nothing
        End If
    Next i