Sheet Verification

Fooser

Board Regular
Joined
Sep 16, 2005
Messages
152
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.

Code:
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.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Does this work for you?

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,499
Members
412,670
Latest member
Khin Zaw Htwe
Top