title: Copy data into sheet if cell = value, works only sometimes?
body: <p>If a cell in column E = "Y", then I want to copy that cell's entire row into Sheet 2, and do this for each worksheet in the workbook, except Sheet 2.</p>
  
  <p>Here's the code I've tried.</p>
  
  <pre><code>Sub Macro1()
  Dim lastrow As Long
  Dim cpyrow As String
  Dim ws As Worksheet
  
  For Each ws In ThisWorkbook.Worksheets
      If ws.Name &lt;&gt; "Sheet2" Then
      For Each cell In Range("E:E")
          If cell.Value = "Y" Then
              lastrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
              cpyrow = cell.Row &amp; ":" &amp; cell.Row
              ws.Range(cpyrow).Copy Destination:=Sheets("Sheet2").Range("A" &amp; lastrow)
          End If
          Next cell
      End If
      Next ws
  End Sub
  </code></pre>
  
  <p>Sometimes it will copy the correct row, but other times it will copy rows whose cell in column E doesn't equal "Y", or it will skip over rows that do. Also why does it give different results when I run it while I'm on different sheets? Shouldn't it run the same and go through every worksheet?</p>