Copying an Entire row using an IF Statement

[Marcus]

Hi

I want to use the below code to copy an entire row to a
new sheet when a = true. Can anyone help !
Sub YTD_Modifer()

r = 2

a = "1st Quarter Quarter"

Do Until Sheets("Data").Cells(r, 2) = ""

r = r + 1

If Sheets("Data").Cells(r, 2) > a Then

copy entire row to a new sheet

End If
Loop
End Sub

Many thanks
Marcus

 

[Rob van Gelder]

Sub test()
Dim i As Long, rng As Range, str As String

str = "1st Quarter Quarter"
Set rng = Worksheets("Sheet2").Rows(1)

With Worksheets("Data")
For i = 2 To .Cells(Rows.Count, 2).End(xlUp).Row
If StrComp(.Cells(i, 2).Value, str) = 1 Then
.Rows(i).Copy rng
Set rng = rng.Offset(1)
End If
Next
End With
End Sub

 

[Marcus]

-----Original Message-----
Sub test()
Dim i As Long, rng As Range, str As String

str = "1st Quarter Quarter"
Set rng = Worksheets("Sheet2").Rows(1)

With Worksheets("Data")
For i = 2 To .Cells(Rows.Count, 2).End(xlUp).Row
If StrComp(.Cells(i, 2).Value, str) = 1 Then
.Rows(i).Copy rng
Set rng = rng.Offset(1)
End If
Next
End With
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel





.