title: finding row based off cell value and then copying paste based on the found row value - object variable not set
body: <p>I'm still a big noobie at VBA so any advice would be so appreciated...</p>
  <p>I am trying to copy a range from multiple worksheets with a specific name to one worksheet in the same workbook. The range is dynamic in that I know what column the range starts and ends on, but i don't know what row it will start and end on.   I have the paste destination figured out, but I need help finding the row the range should start on. The range I want to copy should start after the row in which the cell value = &quot;Open Items:&quot; (i.e. if the row where the cell = &quot;Open Items:&quot; is 3, then i want my range to start at C4)</p>
  <p>I'm having an issue with my .Find formula where it's returning nothing. I am confident that the range that I'm searching for &quot;Open Items:&quot; does have a cell that is equal to that. Please see below for my code:</p>
  <pre><code>Dim ws As Worksheet
  Dim targetws As Worksheet
  Dim FindRow As Range
  Dim openitemrow As Long
  
  Set targetws = Sheets(&quot;targetwsname&quot;)
  
  'loop through each ws
  
  For Each ws In ActiveWorkbook.Worksheets
  
  'sets ws name constraint and then tries to find the starting row in the range
  If ws.Name Like &quot;*&quot; &amp; &quot;Open&quot; &amp; &quot;*&quot; Then
       Set FindRow = ws.Range(&quot;C:C&quot;).Find(&quot;Open Items:&quot;, lookin:=xlValues, lookat:=xlWhole)
       openitemrow = FindRow.Row
  'there needs to be values in c4 for me to copy data over but the data i need to copy is in a different location
            If Not IsEmpty(ws.Cells(4, &quot;C&quot;)) Then
                 ws.Range(&quot;C:F&quot; &amp; openitemrow).End(xlUp).Offset(1).Copy Destination:=targetws.Range(&quot;D&quot; &amp; Rows.Count).End(xlUp).Offset(1)
  
            End If
      End If
  next ws 
  </code></pre>