Search for worksheet in workbook
 

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

Search for worksheet in workbook
 

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
---------------
Jacob Skaria


"Liz" wrote:

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

Search for worksheet in workbook
 

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


"Liz" wrote:

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

Search for worksheet in workbook
 

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

Search for worksheet in workbook
 

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

"Jacob Skaria" wrote in message
...
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
---------------
Jacob Skaria


"Liz" wrote:

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

Search for worksheet in workbook
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Liz" wrote in message
...
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

Search for worksheet in workbook
 

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Liz" wrote in message
...
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