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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,502
Messages
5,832,095
Members
430,110
Latest member
Chyke_mxl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top