Search for worksheet in workbook

[Liz]

I am trying to search a workbook for a specific tab name, and activate that
tab. If that tab does not exist, I would like to exit the sub. I have the
following code, but it does not find the tab when it exists....can u help?

Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If sh.Visible = xlSheetVisible Then
If sh.Name = "My Template" Then
sh.Select
Worksheets("My Template").Activate
Exit For
End If
MsgBox ("The ""My Template"" tab cannot be found in this workbook.")
Exit Sub
End If
Next

 

[Jacob Skaria]

Liz, try the below

On Error Resume Next
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

If sh Is Nothing Then
MsgBox ("The ""My Template"" tab cannot be found in this workbook.")
Else
sh.Activate
End If

If this post helps click Yes

 

[Joel]

I usually use the method you started with. The only thing that was wrong is
you need an additional variable be set to true if the sheet name was found.
I don't like relying on an error to determine if a sheet is found or not
found.

Dim sh As Worksheet
Found = False
For Each sh In ThisWorkbook.Worksheets
If sh.Visible = xlSheetVisible Then
If sh.Name = "My Template" Then
Found = True
Exit For
End If
Next sh

If Found = False Then
MsgBox ("The ""My Template"" tab cannot be found in this workbook.")
Exit Sub
End If

 

[marcus]

Hi Liz

If you change your own code only slightly you get the desired result.

take care

Marcus

Sub test()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If sh.Visible = xlSheetVisible Then
If Not sh.Name = "My Template" Then
'Do Nothing
Else
sh.Select
End If
End If
Next
End Sub

 

[Patrick Molloy]

Set sh = ThisWorkbook.Sheets("My Template")

Liz, try the below

On Error Resume Next
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

If sh Is Nothing Then
MsgBox ("The ""My Template"" tab cannot be found in this workbook.")
Else
sh.Activate
End If

If this post helps click Yes

 

[Don Guillett]

Sub gotosheet()
Dim mysheet As Worksheet
Set mysheet = Sheets("sheet6")
If Not mysheet Is Nothing Then mysheet.Select
End Sub

 

[Don Guillett]

Forgot to error trap

Sub gotosheet()
Dim mysheet As Worksheet
On Error GoTo nono
Set mysheet = Sheets("Iwannagothere")
If Not mysheet Is Nothing Then mysheet.Select
nono:
End Sub