Copy cell values to another worksheet based on cell value

Question

I am trying to copy cell value (A641) from Sheet("WIP") to Sheet("Report") (A3), if (N641) from Sheet("WIP") is a specific value ("text").

I want a loop to check the whole column N (from 641 to the bottom.).

The main table keeps expanding so I want to check to the end of column N every time I run it.

I copied the below from another thread and played around but it is not what I want.

Sub test()
    Dim i As Long, lastRow As Long

    Set wip = Sheets("WIP")
    Set report = Sheets("Report")

    lastRow = wip.Cells(Rows.Count, "N").End(xlUp).Row

    For i = 641 To lastRow
       
        If wip.Range("N" & i).Value = "test" Then
          
            report.Range("A" & i - 638).Value = wip.Range("A" & i).Value
            
        End If
    Next i
End Sub

Answer 1

Something like this should do it:

Sub test()
    Dim i As Long, lastRow As Long, wip As Worksheet, report As Worksheet
    Dim wb As Workbook, v, c As Range

    Set wb = ActiveWorkbook 'or (eg) ThisWorkbook
    Set wip = wb.Worksheets("WIP")
    Set report = wb.Worksheets("Report")

    lastRow = wip.Cells(wip.Rows.Count, "N").End(xlUp).Row
    Set c = report.Cells(Rows.Count, "A").End(xlUp).Offset(1) 'first destination cell
    
    For i = 641 To lastRow
        If wip.Range("N" & i).Value = "test" Then
            v = wip.Range("A" & i).Value 'ColA value
            If Len(v) > 0 Then           'anything to copy?
                c.Value = v              'copy value
                Set c = c.Offset(1)      'next destination cell
            End If
        End If
    Next i
End Sub