VBA条件复制粘贴：POCOM-Main满足K列值为0时复制到Product Backlog

Got it, let's build the VBA code you need. This script will check each cell in POCOM-Main!K2:K1000, and whenever it finds a 0, it'll copy the corresponding value from column A to the first empty row in Product Backlog!A2:A1000.

Here's the full code with comments to walk you through each step:

Sub CopyZeroRelatedValues()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRowTarget As Long
    Dim i As Long
    
    ' Set references to your target worksheets (double-check names match your file!)
    Set wsSource = ThisWorkbook.Worksheets("POCOM-Main")
    Set wsTarget = ThisWorkbook.Worksheets("Product Backlog")
    
    ' Loop through every row from 2 to 1000 in the source sheet
    For i = 2 To 1000
        ' Check if column K in the current row equals 0
        If wsSource.Cells(i, "K").Value = 0 Then
            ' Find the last filled row in column A of the target sheet
            lastRowTarget = wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Row
            
            ' Handle edge case where column A is empty below row 1
            If lastRowTarget < 2 Then lastRowTarget = 1
            
            ' Copy the value from source column A to the next empty row in target column A
            wsTarget.Cells(lastRowTarget + 1, "A").Value = wsSource.Cells(i, "A").Value
        End If
    Next i
    
    ' Optional: Pop up a message to confirm the task is complete
    MsgBox "Values copied successfully!", vbInformation
End Sub

How this works: #

• Worksheet References: We first define clear links to your source and target sheets so the code doesn't get confused about where to read/write data.
• Row Loop: We iterate through rows 2 to 1000 in the source sheet's column K to check for the 0 condition.
• Empty Row Detection: Using End(xlUp), we find the last filled row in the target's column A, then jump to the next empty row to paste the value.
• Direct Value Assignment: Instead of using copy/paste, we directly assign the value—this is faster and avoids issues with clipboard data.

Quick Tips: #

• If you need to expand the range beyond row 1000, just update the 1000 in the For i = 2 To 1000 line to your desired row number.
• To run this code: Press Alt + F11 to open the VBA editor, insert a new module, paste the code, and run the CopyZeroRelatedValues subroutine.

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