Search for worksheet in workbook

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
 
J

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
 
J

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
 
M

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
 
P

Patrick Molloy

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
 
D

Don Guillett

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top