Sheet Verification

[Fooser]

Anyone know of an easier way to verify a sheet exists in my file before creating it? Here is what I did, which seems to work, but is long.

sub test()
'Sheet Verification
  Dim pblnUIFP As Boolean
  Dim pblnUSB As Boolean
  Dim pblnBCIFP As Boolean
  Dim pblnBCSB As Boolean
  For Each ws In Worksheets
    If ws.Name = "GRPEH" Then
      ActiveSheet.Name = "Origin"
    ElseIf ws.Name = "Unicare (IFP)" Then
      pblnUIFP = True
    ElseIf ws.Name = "Unicare (SB)" Then
      pblnUSB = True
    ElseIf ws.Name = "BCCA (IFP)" Then
      pblnBCIFP = True
    ElseIf ws.Name = "BCCA (SB)" Then
      pblnBCSB = True
    End If
  Next ws 'Each ws In Worksheets
'Add sheets if they do not exist
  If pblnUIFP = False Then
    Sheets.Add
    ActiveSheet.Name = "Unicare (IFP)"
  End If
  If pblnUSB = False Then
    Sheets.Add
    ActiveSheet.Name = "Unicare (SB)"
  End If
  If pblnBCIFP = False Then
    Sheets.Add
    ActiveSheet.Name = "BCCA (IFP)"
  End If
  If pblnBCSB = False Then
    Sheets.Add
    ActiveSheet.Name = "BCCA (SB)"
  End If 'pblnUIFP = False
    Sheets("BCCA (SB)").Move after:=Sheets(1)
    Sheets("BCCA (IFP)").Move after:=Sheets(2)
    Sheets("Unicare (SB)").Move after:=Sheets(3)
    Sheets("Unicare (IFP)").Move after:=Sheets(4)
    Sheets("Origin").Select
end sub

Let me know if you guys have any ideas.

 

[Von Pookie]

Does this work for you?

Sub test()
Dim pblnUIFP As Boolean, pblnUSB As Boolean
Dim pblnBCIFP As Boolean, pblnBCSB As Boolean
Dim ws As Worksheet, mySheets As Variant, i As Integer
Dim NewSh As Variant

mySheets = Array("Unicare (IFP)", "Unicare (SB)", _
"BCCA (IFP)", "BCCA (SB)")


On Error Resume Next
Sheets("GRPEH").Name = "Origin"
  
For i = LBound(mySheets) To UBound(mySheets)
    If SheetExists(mySheets(i)) = False Then
        Set NewSh = Worksheets.Add
        With NewSh
            .Name = mySheets(i)
            .Move after:=Sheets(Sheets.Count)
        End With
    End If
Next i
Sheets("BCCA (SB)").Move after:=Sheets(1)
Sheets("BCCA (IFP)").Move after:=Sheets(2)
Sheets("Unicare (SB)").Move after:=Sheets(3)
Sheets("Unicare (IFP)").Move after:=Sheets(4)
Sheets("Origin").Select
  
End Sub

Private Function SheetExists(sname) As Boolean
'   Returns TRUE if sheet exists in the active workbook
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True Else SheetExists = False
End Function