Find Worksheet Name without On Error Resume Next

[Ryan H]

Is there a way to ensure a worksheet is in a workbook without using the On
Error Resume Next statement? I have the name of the worksheet, now I want to
search the workbook for that sheet, how do I do that?

 

[Per Jessen]

Hi Ryan

It can be done like this:

Dim IsThere As Boolean
TargetSh = "Sheet1"
For Each sh In ThisWorkbook.Sheets
If sh.Name = TargetSh Then
IsThere = True
Exit For
End If
Next
If IsThere Then
MsgBox ("Is in the workbook")
Else
MsgBox ("Is not in the workbook")
End If

Regards,
Per

 

[Mike H]

Hi,

Someone will inevitably prove me incorrect but without using ONERROR I think
you have to loop

Dim WorksheetExists As Boolean
For x = 1 To Worksheets.Count
If Sheets(x).Name = "Sheet1" Then
WorksheetExists = True
Exit For
End If
Next
If WorksheetExists Then
MsgBox "Sheet there"
Else
MsgBox "Sheet not there"
End If

Mike

 

[Ryan H]

Thats what I figured I had to do. I wasn't sure if there was a quicker way.

 

[Dave Peterson]

There's nothing wrong with the "on error resume next" approach in VBA. And it's
quicker, too.

Why wouldn't you want to use it?