title: Excel: Lookup table name excel for a given variable
body: <p>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").  </p>
  
  <p>In another sheet called "MasterList" contains all of the order numbers across all project. This list is in column A.</p>
  
  <p>I need a function or Macro that will search all of my sheets (bar MasterList) and will display the sheet name in column B.</p>
  
  <p>Below is what I have in Excel:</p>
  
  <pre><code>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 &lt;&gt; "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
  </code></pre>