check if sheet exists

[Ross]

how would you go about checking to see if a sheet exists
of not?

 

[Ron de Bruin]

One way
(al code in a normal module)

Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function

Use it like this for example

Sub Sheet_Test()
Dim SName As String
If SheetExists("test") = False Then
ActiveSheet.Name = "test"
Else
MsgBox "sorry the sheet exist"
End If
End Sub

 

[Ross]

Thanks guys, I really appreciate the help. I was
wondering if you could provide me qith an example of
passing a workbook through the function as well. Thanks
again.

 

[Chip Pearson]

Ross,

To pass a workbook reference to the function, do something like

If SheetExists("Sheet123",Workbooks("Book2.xls")) = True Then

This will test whether Sheet123 exists in Book2, regardless of
what workbook is active or what workbook the code resides in.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)