VBA技术求助：按条件将指定工作表数据复制至最后工作表

Hey there! As a fellow VBA learner, I totally get figuring out worksheet references and copy-paste logic can be tricky. Let's break down what's off in your current code and fix it to do exactly what you need—copying either the A column data or full rows from "Bulk Update" where column B is 0, to the bottom of your last worksheet.

First, the Revised Code #

Here's a cleaned-up, stable version of your macro:

Public Sub CNPPrevOOS()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRowSource As Long
    Dim lastRowTarget As Long
    Dim i As Long
    
    ' Set references to your source and target (last) worksheet
    Set wsSource = ThisWorkbook.Worksheets("Bulk Update")
    Set wsTarget = ThisWorkbook.Worksheets(ThisWorkbook.Sheets.Count)
    
    ' Find the last row with data in column A of the source sheet
    lastRowSource = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row starting from row 2 (assuming row 1 is headers)
    For i = 2 To lastRowSource
        ' Check if column B is 0 (use "0" if it's text, remove quotes if it's a number)
        If wsSource.Cells(i, 2).Value = 0 Then
            ' Find the next empty row in column A of the target sheet
            lastRowTarget = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row
            ' Copy just column A from source to target
            wsSource.Cells(i, 1).Copy Destination:=wsTarget.Cells(lastRowTarget + 1, 1)
            
            ' Uncomment this line below if you want to copy the entire row instead
            ' wsSource.Rows(i).Copy Destination:=wsTarget.Rows(lastRowTarget + 1)
        End If
    Next i
End Sub

Key Fixes & Explanations #

• Referencing the last worksheet: Your original code had the right idea with ThisWorkbook.Worksheets(ThisWorkbook.Sheets.Count)—that's exactly how you grab the last sheet! The problem was the messy select/active cell stuff after that.
• Ditching Select/ActiveCell: Using Select and ActiveCell makes macros fragile (they break if you click on something else while running). Instead, we directly reference the worksheets and cells with wsSource and wsTarget—way more reliable and faster.
• Handling the 0 value: If column B has numeric 0, don't use quotes. If it's text that says "0", change the check to = "0" instead.
• Finding the next empty row: Every time we copy a row, we re-check the last used row in the target sheet so we don't overwrite existing data.

Just paste this code into your VBA editor, adjust the 0 check if needed, and test it out—it should work smoothly!

内容的提问来源于stack exchange，提问作者t l n