How to verify specific Worksheets are present

  • Thread starter Thread starter TBA
  • Start date Start date
T

TBA

Windows 2k Pro
Excel 2000

I have a Workbook on which I need to do a relatively simple check using VBA
code. I want to scan the Workbook for four specific Worksheets (for example
the worksheets are named "Red", "Blue", "Green" and "Purple"). If any of
these are missing then I'd like to create a new Worksheet with the missing
name(s), AND I'd like to delete any Worksheets that are NOT named with one
of the four mentioned names.

I'm pretty sure I could do this, but I'm afraid my method would not be very
efficient at all. How many steps could this be done in? Or, what is the
most efficient way to do this?

TIA!

-gk-
 
Sub addsheets()
Dim item As Variant
For Each item In _
Array("red", "green", "blue", "purple")
Add_Sheet CStr(item)
Next

End Sub
Private Sub Add_Sheet(sName As String)
Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets(sName)
If Err.Number <> 0 Then
Err.Clear
Set ws = Worksheets.Add
ws.Name = sName
End If
Set ws = Nothing
On Error GoTo 0
End Sub


The Add_Sheet procedure sets the error trap then tries to
assign a specific sheet to the variable WS. If it fails,
the sheet doesn't exist , so the proc adds it and names
it.

Patrick Molloy
Microsoft Excel MVP
 
Back
Top