Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need an Equation in a cell to search a workbook not worksheet | Excel Worksheet Functions | |||
Search 1 worksheet then another for data in large workbook | Excel Programming | |||
Search Data from one Workbook and copy it into another Workbook | Excel Discussion (Misc queries) | |||
Create a search Field within a worksheet to search command buttons | Excel Programming | |||
Need to search workbook for NEW worksheet | Excel Programming |