Copy data into sheet if cell = value, works only sometimes?

Question

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.

Here's the code I've tried.

Sub Macro1()
Dim lastrow As Long
Dim cpyrow As String
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "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 & ":" & cell.Row
            ws.Range(cpyrow).Copy Destination:=Sheets("Sheet2").Range("A" & lastrow)
        End If
        Next cell
    End If
    Next ws
End Sub

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?

Scott Craner Scott Craner · Accepted Answer · 2019-04-19T19:43:38

You missed a parent sheet reference on the loop:

For Each cell In Range("E:E")

Should be

For Each cell In ws.Range("E:E")

Other wise you are testing the active sheet but copying the correct sheet on that row.

Copy data into sheet if cell = value, works only sometimes?

2 Answers