Excel:如何将包含某些文本的行复制到另一个工作表(VBA) [英] Excel: How to copy a row if it contains certain text to another worksheet (VBA)
问题描述
我正在寻找一种能够在工作表中搜索列的marco,如果找到某些文本(在我的情况下为 FAIL一词),则复制整个行的数据/格式并将其粘贴到另一个行中工作表-在我的情况下是工作表4-以及包含该特定文本的任何其他行。
I'm looking to use a marco that would be able to search a column in said sheet and if certain text is found - in my case the word "FAIL" - copy that entire rows data/formatting and paste it into another sheet - sheet 4 in my case - along with any other rows that contained that specific text.
我一直在使用此代码,但是它只复制粘贴一行然后停止,而不是遍历并复制任何带有 FAIL的行
i have been using this code but it only copy pastes one row then stops rather than going through and copying any rows with "FAIL"
Sub Test()
For Each Cell In Sheets(1).Range("H:H")
If Cell.Value = "FAIL" Then
matchRow = Cell.Row
Rows(matchRow & ":" & matchRow).Select
Rows(matchRow & ":" & matchRow).Select
Selection.Copy
Sheets(4).Select
ActiveSheet.Rows(matchRow).Select
ActiveSheet.Paste
Sheets(4).Select
End If
Next
End Sub
第一个帖子并且是VBA的全新内容,因此道歉,如果太含糊。
First post and brand new to VBA so apologies if too vague.
推荐答案
尝试下面的代码(代码内的注释说明):
Try the code below (explanation inside the code as comments):
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
这篇关于Excel:如何将包含某些文本的行复制到另一个工作表(VBA)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!