Copying data from columns in 1 worksheet to another worksheet if a condition is met

[Needinghlp]

Hi VBA experts,

I have some code which is working, however I'm wondering if there is a more efficient code I could use. I have 2 worksheets (sheet 1 and sheet 2). Column J on sheet 2 has values of Yes or No. If column J has a yes I would like to copy the corresponding value in column A on sheet 2 to column b on sheet 1. I would also like to copy the corresponding values in columns C, D & E on sheet 1 to columns C, D & E on sheet 1. The code I'm using is below. Is there a way I can combine the columns I'm copying from in 1 line of code (and similarly combine the columns I'm pasting to in one row of code) i.e. Worksheets("Sheet2").Cells(i, 1, 3, 4, 5).Copy. This doesn't work - but hopefully it gives an idea of what I'm asking.

Private Sub CommandButton1_Click()
A = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

For i = 3 To A

If Worksheets("Sheet2").Cells(i, 10).Value = "Yes" Then

Worksheets("Sheet2").Cells(i, 1).Copy
Worksheets("Sheet1").Cells(i, 2).Select
ActiveSheet.Paste
Worksheets("Sheet2").Cells(i, 3).Copy
Worksheets("Sheet1").Cells(i, 3).Select
ActiveSheet.Paste
Worksheets("Sheet2").Cells(i, 4).Copy
Worksheets("Sheet1").Cells(i, 4).Select
ActiveSheet.Paste

End If

Next

End Sub

 

[mohadin]

Hi
An option

Private Sub CommandButton1_Click()
    A = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 3 To a
        If Worksheets("Sheet2").Cells(i, 10).Value = "Yes" Then
            Worksheets("Sheet2").Cells(i, 1).Copy Worksheets("Sheet1").Cells(i, 2)
            Worksheets("Sheet2").Cells(i, 3).Copy Worksheets("Sheet1").Cells(i, 3)
            Worksheets("Sheet2").Cells(i, 4).Copy Worksheets("Sheet1").Cells(i, 4)
        End If
    Next
End Sub

 

[Fluff]

Another option

Private Sub CommandButton1_Click()
   Dim UsdRws As Long, i As Long
   
   With Worksheets("Sheet2")
      UsdRws = .Cells(Rows.Count, 1).End(xlUp).Row
      For i = 3 To UsdRws
         If .Range("J" & i).Value = "Yes" Then
            .Range("A" & i).Copy Worksheets("Sheet1").Cells(i, 2)
            .Range("C" & i).Resize(, 3).Copy Worksheets("Sheet1").Cells(i, 3)
         End If
      Next i
   End With
End Sub