title: Macro to copy and paste rows with correct first cell
body: <p>I have a spreadsheet with lots of sheets and data, the first cell of each row always being a date. I have made a control sheet (Control CUSTOM), where user types two dates into cells B3 and C3, and then the macro would search all the sheets in the workbook for rows having first cell value BETWEEN these dates, then copy and paste the rows to the summary sheet (Data CUSTOM).</p>
  
  <p>Yet it doesn't work as intended. The macro manages to find correct rows, and copy them, but it pastes it always to the same row, therefore overwriting itself. It also pastes them to wrong sheet (Control CUSTOM).</p>
  
  <p>So far my code looks like this:</p>
  
  <pre><code>Sub DataSearch()
      Dim lngLastRow As Long, lngRow As Long
      Dim strColumn As String
      Dim WS_Count As Integer
      Dim I As Integer
      Dim NextRow As Variant
      Dim Date1 As Variant
      Dim Date2 As Variant
  
  
      Date1 = Sheets("Control CUSTOM").Range("B3")
      Date2 = Sheets("Control CUSTOM").Range("C3")
  
  
  ' Set correct row for paste, always the next empty row
  
  
  
  ' Set WS_Count equal to the number of worksheets in the active workbook.
  WS_Count = ActiveWorkbook.Worksheets.Count
  
  ' Begin the loop.
  For I = 1 To WS_Count
  
      strColumn = "A"
      With ActiveWorkbook.Worksheets(I)
          lngLastRow = .Cells(.Rows.Count, strColumn).End(xlUp).Row
          For lngRow = 2 To lngLastRow
          Set NextRow = Range("A" &amp; Sheets("Data CUSTOM").UsedRange.Rows.Count + 1)
          If IsDate(.Cells(lngRow, strColumn).Value) And .Cells(lngRow, strColumn).Value &gt;= Date1     And .Cells(lngRow, strColumn).Value &lt;= Date2 Then
              .Rows(lngRow).Copy
              NextRow.PasteSpecial Paste:=xlValues, Transpose:=False
          Application.CutCopyMode = False
  
  
          End If
          Next lngRow
      End With
      Next I
  End Sub
  </code></pre>
  
  <p>Hope you guys can help me. I have some experience with VBA, but all these multiple loops this propably needs just goes way over my head.</p>