search entire workbook to return sheet name

[currybai]

Hi, I have a file with unique account numbers and another file with multiple sheets in all different formats. I want to return the sheet name in which the unique account number appears in. Is that possible?

 

[JoeMo]

Hi, I have a file with unique account numbers and another file with multiple sheets in all different formats. I want to return the sheet name in which the unique account number appears in. Is that possible?

You can use the Find feature with the "Within" option set to Workbook.

 

[lozzablake]

Some code like below will work for you

<code>
Sub FindAccountNumber()

Dim TargetWorkbook As Workbook
Dim aSheet As Worksheet
Dim AccountToFind As String
Dim Found As Range
Dim i As Integer


Set TargetWorkbook = Workbooks("NameofWorkbookHere.xlsm")

AccountToFind = "123456"
i = 1

For Each aSheet In TargetWorkbook.Sheets

Set Found = aSheet.Range("a1:a64000").Find(AccountToFind)
If Not Found Is Nothing Then
ThisWorkbook.Sheets("SheetToStoreName").Cells(i, 1) = aSheet.Name
i = i + 1
Exit For
End If

Next aSheet

End Sub
</code>

If you have a lot of account numbers to find then you could put a loop around it. Also you will need to alter the search range (a1:a64000 in code) to wherever your account numbers will actually be

 

[Biz]

Another variant

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _
                          Col_num As Integer, Optional Range_look As Boolean, Optional SheetName As Boolean)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Use VLOOKUP to Look across ALL Worksheets and stops _
'at the first match found.
'Now in any cell put in the Function like this:
'=VLOOKAllSheets("Dog",C1:E20,2,FALSE)
'Where "Dog" is the value to find
'" " C1:E20 is the range to look in the first column and find "Dog"
'" " 2 is the relative column position in C1:E20 to return return our result from.
'" " FALSE (or ommited) means find and exact match of "Dog"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim wSheet As Worksheet
Dim vFound
Application.Volatile True
On Error Resume Next
'The Parent of a Range is a worksheet.
'The Parent of a Worksheet is a workbook. someRange.Parent.Parent is a workbook.
'The code needs to "find" the workbook, in order to loop through all of its worksheets.
 
    For Each wSheet In Tble_Array.Parent.Parent.Worksheets
        With wSheet
        Set Tble_Array = .Range(Tble_Array.Address)
            vFound = WorksheetFunction.VLookup _
            (Look_Value, Tble_Array, _
            Col_num, Range_look)
        End With
        If Not IsEmpty(vFound) Then Exit For
    Next wSheet
    VLOOKAllSheets = vFound
    If IsError(vFound) Then Exit Function
    Set Tble_Array = Nothing
    If SheetName = True Then
        VLOOKAllSheets = wSheet.Name
    End If
End Function

New Formula using UDF
=VLOOKAllSheets($A2,'[Alcohol DB.xls]Spirits'!$A:$A,COLUMNS($A:$A),FALSE,TRUE)