VBA 将符合条件的行复制到另一个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21074874/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA copy rows that meet criteria to another sheet
提问by Anca
I am new to VBA...I want to copy a row from Sheet2 to Sheet1 if the first cell in this row says X and then do so for all the rows that meet this criteria. I have an error in the If condition...I don't know how to fix it.
我是 VBA 新手...如果该行中的第一个单元格显示 X，我想将一行从 Sheet2 复制到 Sheet1，然后对满足此条件的所有行执行此操作。我在 If 条件中有错误...我不知道如何修复它。
Sub LastRowInOneColumn()
'Find the last used row in a Column: column A in this example
Worksheets("Sheet2").Activate
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
MsgBox (LastRow)
For i = 1 To LastRow
If Worksheet.Cells(i, 1).Value = "X" Then
ActiveSheet.Row.Value.Copy _
Destination:=Hoja1
End If
Next i
End Sub
回答by Dmitry Pavliv
You need to specify workseet. Change line
您需要指定工作表。换线
If Worksheet.Cells(i, 1).Value = "X" Then
to
到
If Worksheets("Sheet2").Cells(i, 1).Value = "X" Then
UPD:
更新：
Try to use following code (but it's not the best approach. As @SiddharthRout suggested, consider about using Autofilter):
尝试使用以下代码（但这不是最好的方法。正如@SiddharthRout 建议的那样，考虑使用Autofilter）：
Sub LastRowInOneColumn()
Dim LastRow As Long
Dim i As Long, j As Long
'Find the last used row in a Column: column A in this example
With Worksheets("Sheet2")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
MsgBox (LastRow)
'first row number where you need to paste values in Sheet1'
With Worksheets("Sheet1")
j = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
For i = 1 To LastRow
With Worksheets("Sheet2")
If .Cells(i, 1).Value = "X" Then
.Rows(i).Copy Destination:=Worksheets("Sheet1").Range("A" & j)
j = j + 1
End If
End With
Next i
End Sub
回答by Munkeeface
After formatting the previous answer to my own code, I have found an efficient way to copy all necessary data if you are attempting to paste the values returned via AutoFilterto a separate sheet.
在将上一个答案格式化为我自己的代码后，如果您尝试将通过返回的值粘贴AutoFilter到单独的工作表中，我找到了一种复制所有必要数据的有效方法。
With .Range("A1:A" & LastRow)
.Autofilter Field:=1, Criteria1:="=*" & strSearch & "*"
.Offset(1,0).SpecialCells(xlCellTypeVisible).Cells.Copy
Sheets("Sheet2").activate
DestinationRange.PasteSpecial
End With
In this block, the AutoFilterfinds all of the rows that contain the value of strSearchand filters out all of the other values. It then copies the cells (using offset in case there is a header), opens the destination sheet and pastes the values to the specified range on the destination sheet.
在此块中，AutoFilter查找包含值的所有行strSearch并过滤掉所有其他值。然后复制单元格（如果有标题，则使用偏移量），打开目标工作表并将值粘贴到目标工作表上的指定范围。
