Copy If Date less than Today

[billandrew]

Hello all

Trying to copy all rows to another sheet if column 8 value is not today's date.

this is the loop I am using.

Thank you

Sub copyif()

Dim Lr As Long, lr2 As Long

Lr = Sheets("Report").Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To Lr

If Sheets("Report").Cells(i, 8).Value < Date Then
Rows(i).Copy Destination:=Sheets("Sheet1").Range("A").End(xlUp).Row
lr2 = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Report").Rows(1).Copy Destination:=Sheets("Sheet1").Rows(1)

End If

Next i


End Sub

 

[MARK858]

Although there are faster ways, just to correct your syntax

Rows(i).Copy Destination:=[COLOR="#FF0000"]Sheets("Sheet1").Range("A").End(xlUp).Row[/COLOR]

could be written as

Rows(i).Copy Destination:=Sheets("Sheet1").Range("A") [COLOR="#FF0000"]& Rows.count[/COLOR]).End(xlUp).[COLOR="#FF0000"]Offset(1)[/COLOR]

or

 Sheets("Report").Rows(i).Copy Destination:=Sheets("Sheet1").[COLOR="#FF0000"]Rows[/COLOR](Sheets("Sheet1").Range("A" & Rows.Count).[COLOR="#FF0000"]End(xlUp).Row[/COLOR]).[COLOR="#FF0000"]Offset(1)[/COLOR]

i.e.

Sub copyif()

    Dim Lr As Long, i As Long

    Lr = Sheets("Report").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To Lr

        If Sheets("Report").Cells(i, 8).Value < Date Then
            Sheets("Report").Rows(i).Copy Destination:=Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)

            Sheets("Report").Rows(1).Copy Destination:=Sheets("Sheet1").Rows(1)

        End If

    Next i


End Sub

or

Sub copyif()

    Dim Lr As Long, i As Long

    Lr = Sheets("Report").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To Lr

        If Sheets("Report").Cells(i, 8).Value < Date Then
            Sheets("Report").Rows(i).Copy Destination:=Sheets("Sheet1").Rows(Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row).Offset(1)

            Sheets("Report").Rows(1).Copy Destination:=Sheets("Sheet1").Rows(1)

        End If

    Next i


End Sub

Btw the codes above are for less than today's date as per your code, if you want different to today's date as per the question then change < to <> in both codes.

 

[billandrew]

Thank you for the code both work perfectly. Most important thank you for the syntax correction, great help.