"Find" a tab in a sheet, instead of having to loop

tvoltagg

Board Regular
Joined
Mar 20, 2006
Messages
159
I have a process that grabs information from a specific tab from another file.

In order to find the appropriate tab, i loop through each tab, waiting until the tab name equals the current search.

Is there a way to skip this looping counter and just FIND the tab that equals "ABC"?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This:

Worksheets("ABC")

will return that worksheet (if it exists in the ActiveWorkbook).
 

tvoltagg

Board Regular
Joined
Mar 20, 2006
Messages
159
Thanks... that was easy, but here's a follow up.

I'm actually using a reference, not the name itself, so I'm saying:

Worksheets(Tabs).Select where Tabs contains which tab name i'm looking for. it works fine. but now i'd like to pop a msgbox if the tab is not found in the file, and i can't get it to work. here's what i have:

<code>
If IsError(Worksheets(Tabs).Select) Then
MsgBox (Tabs & " was not located in the file so " & RecipName & " did not receive that file.")
counter2 = counter2 + 1
Else
....
</code>

but that gives me a subscript out of range. How do i say:
If Worksheets(Tabs).Select is not found ..?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

you might use something like

Code:
If WksExists(tabs) Then
MsgBox (tabs & " was not located in the file so " & RecipName & " did not receive that file.")
counter2 = counter2 + 1
Else
'''
End If
function
Code:
Function WksExists(wksName As Variant) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function
kind regards,
Erik
 

tvoltagg

Board Regular
Joined
Mar 20, 2006
Messages
159
actually had to make it

If NOT WksExists(tabs) Then

but it worked great! thanks guys.
 

Forum statistics

Threads
1,136,515
Messages
5,676,302
Members
419,619
Latest member
jalme

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top