Cut Row IF = to THEN Paste to Different Sheet with VBA

[ChrisOK]

Need code to look at "S3" of Sheet1, IF="#N/A", THEN cut that Row 3 and paste it onto first available open row on Sheet2.
Then, continue to move down Sheet1 looking at each row for rows meeting that criteria (loop through Sheet1 to end).

I was trying to use the below code as a start, but not sure how to conform it to use "IF/THEN" criteria.
I'm open to either conforming this -- or throwing it out and using a better solution! Thanks!

Sub CutPasteRow_MODULE_11()
 
Application.ScreenUpdating = False
 Worksheets("Sheet1").Select
 ActiveCell.EntireRow.Select
 Selection.Cut

Sheets("Sheet2").Select
 Range("A65536").End(xlUp).Offset(1, 0).Select
 ActiveSheet.Paste
 Application.CutCopyMode = False

Sheets("Sheet1").Select
 Selection.Delete Shift:=xlUp
 ActiveCell.Select
 Application.ScreenUpdating = True

End Sub

 

[dermie_72]

See if this code works any better for you. Using filters instead of looping.

Sub ChrisOK()
Application.ScreenUpdating = False
LR = Sheets("Sheet1").Cells(Rows.Count, "S").End(xlUp).Row + 1
LR1 = Sheets("Sheet2").Cells(Rows.Count, "S").End(xlUp).Row + 1
Sheets("Sheet1").Range("A2").EntireRow.Insert
With Range("S2:S" & LR)
    .AutoFilter
    .AutoFilter Field:=1, Criteria1:="#N/A"
    .SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & LR1)
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Sheets("Sheet2").Range("A" & LR1).EntireRow.Delete
Application.ScreenUpdating = True
End Sub

 

[ChrisOK]

FANTASTIC Dermie!! Works like a charm!
Great idea to use the autofilter! Learned something new!

(I clicked LIKE on your post to make sure you got the much deserved kudos!)

 

[dermie_72]

Thanks for the feedback Chris, greatly appreciate it.

Thats what this forum is about, I learnt the filter technique from someone else here when I asked a similar question.