Excel:如何使用"复制粘贴"命令到选定的工作表区域(VBA)

Question

扩展问题来自- Excel：如果行包含某些文本，如何将行复制到另一个工作表(VBA(

我希望修改下面的代码，以便它能够从工作表 2 复制到工作表 4，粘贴相邻的 (L：U(？ 见图片。

图2

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

评论 用图片回复 图片3

Answer 1

像这样？

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