Excel: How to use Copy Paste command to selected sheet area (VBA)

Question

Extended Question from- Excel: How to copy a row if it contains certain text to another worksheet (VBA)

I'm looking to modify the below code so that it's able to copy from sheet 2 to sheet 4, pasted adjacent (L:U)? see pictures.

pic2

Option Explicit

Sub Test()

Dim Cell As Range

With Sheets(1)
' loop column H untill last cell with value (not entire column)
For Each Cell In .Range("H1:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)
    If Cell.Value = "FAIL" Then
         ' Copy>>Paste in 1-line (no need to use Select)
        .Rows(Cell.Row).Copy Destination:=Sheets(4).Rows(Cell.Row)
    End If
Next Cell
End With

End Sub

Comment Reply with picture pic3

Answer 1

Like this?

Option Explicit

Sub Test()

Dim Cell As Range

With Sheets(1)
' loop column H untill last cell with value (not entire column)
For Each Cell In .Range("H1:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)
    If Cell.Value = "FAIL" Then
         ' Copy>>Paste in 1-line (no need to use Select)
        .Range("A" & Cell.Row & ":J" & Cell.Row).Copy Destination:=Sheets(4).Range("L" & Sheets(4).Cells(Sheets(4).Rows.Count, "L").End(xlUp).Row + 1)
    End If
Next Cell
End With

End Sub

Macro to copy links:

Option Explicit

Sub Test()

Dim Cell As Range

With Sheets(1)
' loop column H untill last cell with value (not entire column)
For Each Cell In .Range("H1:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)
    If Cell.Value = "FAIL" Then
        .Range("A" & Cell.Row & ":J" & Cell.Row).Copy
         ' Paste as Links requires to select a destination cell
        Sheets(4).Range("L" & Sheets(4).Cells(Sheets(4).Rows.Count, "L").End(xlUp).Row + 1).Select
        ActiveSheet.Paste Link:=True
    End If
Next Cell
End With

End Sub