Does a sheet exist

[Randall]

Given a sheet name, I am trying to write some code that returns TRUE if a
sheet exists and FALSE if it does not.

Ex: SheetName = "Bob", Returns TRUE if there is a sheet by this name,
returns FALSE if it does not.

Thanks

 

[Dave Peterson]

You could use a function (from Chip Pearson) to test the existence of a sheet.

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function

'and you can use it like:
....
if worksheetexists("myname",activeworkbook) then

========
Lots of times, I'll just check with this kind of code:

Dim wks as worksheet
....
set wks = nothing
on error resume next
set wks = worksheets("somesheetname")
on error goto 0
if wks is nothing then
'not there
else
'it is there
end if