Search for worksheet in workbook

  • Thread starter Thread starter Liz
  • Start date Start date
L

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
 
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
 
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
 
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
 
Set sh = ThisWorkbook.Sheets("My Template")

Jacob Skaria said:
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
Click to expand...
 
Sub gotosheet()
Dim mysheet As Worksheet
Set mysheet = Sheets("sheet6")
If Not mysheet Is Nothing Then mysheet.Select
End Sub
 
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
 
Back
Top