Excel:如何使用“复制粘贴”命令到选定图纸区域(VBA)

这样地?

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

复制链接的宏:

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