excel - VBA将符合条件的行复制到另一个工作表

Question

我是 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

Answer 1

您需要指定工作集。换行

If Worksheet.Cells(i, 1).Value = "X" Then

到

If Worksheets("Sheet2").Cells(i, 1).Value = "X" Then

更新:

尝试使用以下代码(但这不是最好的方法。正如 @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