COPYING Rows to Another Worksheet Based on Criteria - Problem with Code - HELP is appreciated!

[kescco]

WORKSHEET 1 = MAIN
WORKSHEET 2 = BINS

Copying all rows in BINS with 999 in Column C to MAIN.

Problem is that my 999 rows start in Row 1 of BINS and thus the top
entry is being left out (works from the bottom). For now, I have added a
blank row at the top. Is there a better way to fix this?

All help is appreciated.

Dim lr2 As Long, r As Longlr = Sheets("Bins").Cells(Rows.Count, "C").End(xlUp).Row
lr2 = Sheets("Main").Cells(Rows.Count, "F").End(xlUp).Row
For r = lr To 2 Step -1
    If Sheets("Bins").Range("C" & r).Value = "999" Then
        Sheets("Bins").Rows(r).Copy Destination:=Sheets("Main").Range("A" & lr2 + 1)
        lr2 = Sheets("Main").Cells(Rows.Count, "F").End(xlUp).Row
    End If
Next r

 

[Trebor76]

Hi kesco,

Just change the 2 in this line of code...

For r = lr To 2 Step -1

...to 1

Note you don't have to loop backwards when copying only really when you are deleting or moving rows.

Regards,

Robert