title: VBA: Using a Find-like function across all workbook sheets without looping
body: <p>I have some code that loops through a series of sheets in a workbook and tries to find a match to a value in another sheet.</p>
  
  <pre><code>Private Sub MatchData(NewMIARep As Worksheet, MaxRow As Long, wkbFinalized As Workbook)
  Dim wksFinalized As Worksheet
  Dim lCount As Long
  Dim lFinMaxRow As Long
  Dim DataRange As Variant
  Dim SearchRange As Variant
  Dim FoundRange As Range
  
  Application.Calculation = xlCalculationManual
  
  With NewMIARep
  
      DataRange = .Range("J2:K" &amp; MaxRow)
      SearchRange = .Range("A2:A" &amp; MaxRow)
  
      For Each wksFinalized In wkbFinalized.Sheets
          lFinMaxRow = GetMaxRow(wksFinalized)
          If lFinMaxRow &gt; 1 Then
              For lCount = 1 To MaxRow - 1
                  If Len(DataRange(lCount, 1)) = 0 And Len(DataRange(lCount, 2)) = 0 Then
                      Set FoundRange = wksFinalized.Range("A2:A" &amp; lFinMaxRow).Find(What:=SearchRange(lCount, 1), _
                          LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                          MatchCase:=False, SearchFormat:=False)
                      If Not FoundRange Is Nothing Then
                          DataRange(lCount, 1) = FoundRange.Offset(ColumnOffset:=12).Value
                          DataRange(lCount, 2) = FoundRange.Offset(ColumnOffset:=2).Value
                          Set FoundRange = Nothing
                      End If
                  End If
              Next lCount
          End If
      Next wksFinalized
  
  .Range("J2:K" &amp; MaxRow).Value = DataRange
  .Range("J2:J" &amp; MaxRow).NumberFormat = "mm/dd/yyyy"
  
  End With
  
  Application.Calculation = xlCalculationAutomatic
  </code></pre>
  
  <p>As this goes through every sheet in <code>wkbFinalized</code>, and each sheet has 30,000-60,000 or so records, and I loop another 5,000-6,000 times within that loop for each of the items I want to search for, this tends to slow down quite a bit (not the fastest machine in the world, but I have no choice in the matter).</p>
  
  <p>I know I can't do this specifically, but I'm looking for a function that will work <em>like</em><br>
  <code>wkbFinalized.Find(...)</code><br>
  vs.<br>
  <code>wkbFinalized.Sheets(n).Find(...)</code>.  </p>
  
  <p>Does such a function exist? </p>
  
  <p><strong>OR</strong> Is there a way to somehow preload all the data from all sheets into one range before searching, so that the internal loop only runs once? (and would this be any more or less efficient?)</p>