关于VBA多条件If语句实现符合条件行跨工作表复制的技术疑问

Hey Laura, let's work through your VBA code problems together—there are a couple of key issues causing your output to behave unexpectedly, plus we can simplify your conditional logic to make it cleaner.

First: Simplify Your Conditional Logic #

Let's start with your If statement. The third condition (Worksheets("Sheet2").Cells(i, 1).Value = "X" And Worksheets("Sheet2").Cells(i, 2).Value = "Y") is redundant. When both column 1 is "X" and column 2 is "Y", the first condition (= "X") already triggers the Or check, so you don't need to include this third case. Your simplified condition can just be:

If Worksheets("Sheet2").Cells(i, 1).Value = "X" Or _
   Worksheets("Sheet2").Cells(i, 2).Value = "Y" Then

This will still capture all the cases you need: rows where column 1 is "X", column 2 is "Y", or both.

The Real Culprits Behind Your Abnormal Output #

Your main issues are in how you're copying rows and specifying the destination:

1. Incorrect Row Copy Syntax
   Your line ActiveSheet.Row.Value.Copy has two mistakes:

   • You need to reference the specific row with Rows(i) (not just Row)
   • You don't need .Value when copying entire rows—just copy the row directly.

2. Static Destination Causes Overwriting
   Using Destination:=Hoja1 will paste every matching row starting at cell A1 of Hoja1, which means each new row overwrites the previous one. Instead, you need to find the last used row in Hoja1 and paste below it.

3. Avoid Using Activate for Stability
   Activating sheets can lead to unexpected behavior if the user clicks on another sheet while the macro runs. It's better to reference worksheets directly without activating them.

Corrected Full Code #

Here's the revised script that fixes all these issues:

Sub CopyRowsWithCriteria()
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim lastRowSource As Long
    Dim lastRowDest As Long
    Dim i As Long
    
    ' Set references to your worksheets (change names if needed)
    Set wsSource = ThisWorkbook.Worksheets("Sheet2")
    Set wsDest = ThisWorkbook.Worksheets("Hoja1") ' Make sure this name matches your actual sheet
    
    ' Find last used row in source sheet (column A)
    lastRowSource = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row in source sheet
    For i = 1 To lastRowSource
        ' Check your simplified criteria
        If wsSource.Cells(i, 1).Value = "X" Or wsSource.Cells(i, 2).Value = "Y" Then
            ' Find last used row in destination sheet (column A)
            lastRowDest = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row
            
            ' Copy the row to the next empty row in destination
            wsSource.Rows(i).Copy Destination:=wsDest.Cells(lastRowDest + 1, 1)
        End If
    Next i
End Sub

Key Changes Explained #

• We define explicit worksheet objects (wsSource and wsDest) to avoid relying on ActiveSheet.
• For each matching row, we find the last empty row in the destination sheet so rows are appended instead of overwritten.
• Fixed the row copy syntax to correctly reference Rows(i).
• Simplified the conditional logic to remove redundant checks.

This should resolve your abnormal output issue and make your macro more reliable!

内容的提问来源于stack exchange，提问作者Laura