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

[tvoltagg]

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"?

 

[Andrew Poulsom]

This:

Worksheets("ABC")

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

 

[tvoltagg]

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]

Hi,

you might use something like

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

Function WksExists(wksName As Variant) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

kind regards,
Erik

 

[tvoltagg]

actually had to make it

If NOT WksExists(tabs) Then

but it worked great! thanks guys.