Look for a worksheet of specific name

[Clint Rose]

Hello all,

I have a feeling this should be basic, but I'm stumped.

I need to be able to look through all the worksheets in a workbook to see if
the workbook contains a sheet by a certain name.

I don't know if the 'Exists Method' can be used for this or something else,
or if I have to create my own function.

If I have to create my own function, what is the most efficient way to
approach that?

Thanks for your time,

- Clint

[Gary Brown]

Clint,
per John Walkenbach-
'/=======================================/
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
'/=======================================/
HTH,
--
Gary Brown
gary....@kinneson.com
www.kinneson.com

"Clint Rose" <clintrose@hotmailDOTcom> wrote in message
news:usL51sCqBHA.2344@tkmsftngp07...

[Clint Rose]

Thank you for the quick, effective response!!

- Clint

"Gary Brown" <gary....@kinneson.com> wrote in message
news:#WfdevCqBHA.2212@tkmsftngp04...

[J.E. McGimpsey]

One way:

Dim ws As Worksheet

On Error Resume Next
Set ws = Worksheets("mySheet")
On Error GoTo 0

If Not ws Is Nothing Then
MsgBox "Hooray! Sheet ""mySheet"" exists!"
'Do Stuff
Else
MsgBox "Sadly, Sheet ""mySheet"" doesn't exist."
'Do other stuff
End If

In article <usL51sCqBHA.2344@tkmsftngp07>, Clint Rose
<clintrose@hotmailDOTcom> wrote:

--
Email address ROT13 encoded. Decode for real address.

[Chip Pearson]

Clint,

Try something like

Function SheetExists(SheetName As String, _
Optional ByVal WB As Workbook) As Boolean
On Error Resume Next
If WB Is Nothing Then
Set WB = ThisWorkbook
End If
SheetExists = Len(WB.Worksheets(SheetName).Name)
End Function

You can then call this from you other code as

If SheetExists("Sheet1") = True Then
MsgBox "Sheet exists"
Else
MsgBox "Sheet does not exist"
End If

or

If SheetExists("Sheet3", Workbooks("Book2")) = True Then
MsgBox "Sheet exists"
Else
MsgBox "Sheet does not exist"
End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Clint Rose" <clintrose@hotmailDOTcom> wrote in message
news:usL51sCqBHA.2344@tkmsftngp07...