Copy and Paste based on cell value

[DPD]

Hello,

I have two sheets. Phase I and Phase II. Phase I I have columns A-J. Im looking for a macro which would copy columns A-F and paste it to sheet Phase II A5 when value of column J on sheet Phase I is yes.



Sub Yes1()
'Copy from one sheet to another based on cell value
Application.ScreenUpdating = False
Dim i As Integer
Dim lastRow As Long
Dim LastroWB As Long
lastRow = Cells(Rows.Count, "J").End(xlUp).Row + 1
LastroWB = Sheets("Phase II").Cells(Rows.Count, "A").End(xlUp).Row + 1

For i = 2 To lastRow

If Cells(i, 10).Value = "YES" Then
Range(Cells(i, 1), Cells(i, 6)).Copy Sheets("Phase II").Rows(LastroWB)
LastroWB = LastroWB + 1
End If
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

[Sandler]

Sub Yes1()


Dim cRange As Range, LastRow As Long


Worksheets("Phase I").Select
Columns("J:J").Select
Selection.AutoFilter
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
ActiveSheet.Range("$J$1:$J" & LastRow).AutoFilter Field:=1, Criteria1:="yes"
Set cRange = Range("A1:F" & LastRow)
Range("A1:F9").Select
Selection.Copy
Sheets("Phase II").Select
Range("A1").Select
ActiveSheet.Paste
End Sub

[mdmackillop]

Hi Sandler
Try to avoid Selection. Also, this row "Range("A1:F9").Select" should be deleted.

Sub Yes2()
Dim WS1 As Worksheet, WS2 As Worksheet
Set WS1 = Worksheets("Phase I")
Set WS2 = Worksheets("Phase II")

With WS1
.Columns("J:J").AutoFilter Field:=1, Criteria1:="yes"
.Range("A:F").Copy WS2.Range("A5")
.Columns("J:J").AutoFilter
End With
End Sub

[Sandler]

Thanks md, I used the macro recorder on a sample selection to figure this one out and made some changes. I appreciate your concise code and have learned quite a bit from vbaexpress. Thanks :)

Also, you were more diligent than I was, only now do I see the A5 part in Phase II of the original question.

Is the xlUp unnecessary in this code? Does Excel process fliters fast for the full column?

Thanks :)