Check for sheet name

[Mike Fogleman]

How would I check to see if there is a sheet named "Sheet1" in the active
workbook.

Sub CheckFor New Sheet()

If sheets("Sheet1") does not exist in this WB Then
MsgBox ("No sheet has been added")
Exit Sub
Else
End If

continue...

End Sub

 

[Barry Pettis]

Mike

What you need to do is to loop through the worksheets collection,
while looking at each worksheets name. The following code should serve
as an example

Sub FindASheet()

' Local Variables
Dim wks As Worksheet
Dim blnWksPresent As Boolean

For Each wks In Application.Worksheets
If wks.Name = "Feed Sheet (1)" Then
blnWksPresent = True
End If
Next wks
If Not blnWksPresent Then Exit Sub

End Sub

 

[Doug Glancy]

Mike,

Here's another method:

Sub test()

Dim sh As Worksheet

On Error Resume Next
Set sh = ActiveWorkbook.Worksheets("Sheet1")
If Err <> 0 Then
MsgBox "no Sheet1"
Else
MsgBox "Sheet1 exists"
End If
On Error GoTo 0

End Sub

hth,

Doug

 

[Rocky McKinley]

Chip gave this as an answer once I thought it was about as short as
possible.

Function WorksheetExists(WSName As String, Optional WB As Workbook =
Nothing) As Boolean
On Error Resume Next
WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook,
WB).Worksheets(WSName).Name))
End Function

 

[Mike Fogleman]

Thanks guys, a loop worked fine because there can only be 1 or 2 sheets
total in the book.