vba - Excel: Lookup table name excel for a given variable

Question

I have one Workbook with multiple projects. Each project has it's own sheet. In each of these sheets there are columns for order numbers ("OrderNub").

In another sheet called "MasterList" contains all of the order numbers across all project. This list is in column A.

I need a function or Macro that will search all of my sheets (bar MasterList) and will display the sheet name in column B.

Below is what I have in Excel:

Option Explicit
Function FindMyOrderNumber(strOrder As String) As String

    Dim ws As Worksheet
    Dim rng As Range

    For Each ws In Worksheets
        If ws.CodeName <> "MasterList" Then
            Set rng = Nothing
            On Error Resume Next
                FindMyOrderNumber = ws.Name
            On Error GoTo 0
            If Not rng Is Nothing Then
                FindMyOrderNumber = ws.Range("A1").Value
                Exit For
            End If
        End If
    Next

    Set rng = Nothing
    Set ws = Nothing

End Function

Answer 1

Option Explicit
Function FindMyOrderNumber(strOrder As String) As String

    Dim ws As Worksheet
    Dim rng As Range

    For Each ws In Worksheets
        If ws.CodeName <> "MasterList" Then
            Set rng = Nothing
            On Error Resume Next
                Set rng = ws.Columns(1).Find(strOrder)
            On Error GoTo 0
            If Not rng Is Nothing Then
                FindMyOrderNumber = ws.Name
                Exit For
            End If
        End If
    Next

    Set rng = Nothing
    Set ws = Nothing

End Function

Assumptions:

1. Your project sheets us Table objects. If they don't, you need to edit line 11 to point to whatever range contains the OrderNub data.
2. If not tables, then your projects at least use the exact same layout. In that case, you could change line 11 to something like: Set rng = ws.Range("C1").EntireColumn.Find(strOrder)
3. The code name of the master list is MasterList. This is not the same as the worksheet name as seen on the tab. This is the VBA code name. I prefer to use that as it is less likely to be changed and break the check. You can find the codename in the VBA editor. For instance, in this screenshot, the codename for the first worksheet is shtAgingTable and the name - as shown on the tab in Excel - is AgingTable.

This is a function, not a subroutine. That means you don't run it once. It's meant to be used like any other built-in function like SUM, IF, whatever. For instance, you can use the following formula in cell B2:

=FindMyOrderNumber($A2)