基于Internal Asset ID条件的跨工作簿行复制技术问询

First, let's restate the requirement to make sure we're aligned:

若Internal Asset ID（B列）唯一，则无论F列是否标记为选中，均复制该行；若Internal Asset ID不唯一（即B列存在重复值），则仅复制对应Internal Asset ID下F列标记为选中的行。需复制第3、5、7、8、9行，数据位于Workbook1:Sheet1，需复制至Workbook2:Sheet2

Using VBA for Automated Copying #

This method handles the logic automatically, so you don't have to manually sort or filter rows. Here's how to set it up:

1. Open both Workbook1 and Workbook2 in Excel, and confirm Sheet1 (source) and Sheet2 (destination) exist.
2. Press Alt + F11 to open the VBA Editor.
3. Right-click your workbook in the Project Explorer > Insert > Module.
4. Paste the following code into the module:

Sub CopyTargetRows()
    Dim sourceWB As Workbook, destWB As Workbook
    Dim sourceWS As Worksheet, destWS As Worksheet
    Dim lastRow As Long, i As Long, destRow As Long
    Dim assetID As String
    Dim idCount As Long
    
    ' Define source workbook and worksheet
    Set sourceWB = Workbooks("Workbook1.xlsx") ' Update with your actual file name (include extension)
    Set sourceWS = sourceWB.Sheets("Sheet1")
    
    ' Define destination workbook and worksheet
    Set destWB = Workbooks("Workbook2.xlsx")
    Set destWS = destWB.Sheets("Sheet2")
    
    destRow = 1 ' Start pasting at row 1; adjust if Sheet2 has headers (e.g., set to 2)
    
    ' Find the last row with data in column B of source sheet
    lastRow = sourceWS.Cells(sourceWS.Rows.Count, "B").End(xlUp).Row
    
    ' Loop through rows starting from row 3 (as per your target rows)
    For i = 3 To lastRow
        assetID = sourceWS.Cells(i, "B").Value
        
        ' Count how many times this Internal Asset ID appears in column B
        idCount = Application.WorksheetFunction.CountIf(sourceWS.Range("B:B"), assetID)
        
        ' Check if the row meets the copy condition
        If idCount = 1 Then
            ' Unique ID: copy the entire row
            sourceWS.Rows(i).Copy destWS.Rows(destRow)
            destRow = destRow + 1
        Else
            ' Duplicate ID: only copy if column F is marked "选中"
            ' Adjust this line if F uses a checkbox instead of text (see note below)
            If sourceWS.Cells(i, "F").Value = "选中" Then
                sourceWS.Rows(i).Copy destWS.Rows(destRow)
                destRow = destRow + 1
            End If
        End If
    Next i
    
    MsgBox "Row copying completed successfully!", vbInformation
End Sub

Key Notes #

• If Column F uses checkboxes instead of text: Replace the line If sourceWS.Cells(i, "F").Value = "选中" Then with something like If sourceWS.Shapes("CheckBox" & i).Value = xlOn Then (adjust the checkbox name to match how they're labeled in your sheet).
• File paths: If your workbooks aren't open, add lines to open them directly, e.g., Set sourceWB = Workbooks.Open("C:\Your\File\Path\Workbook1.xlsx").
• Destination start row: If Sheet2 has header rows, change destRow = 1 to destRow = 2 (or the first empty row in your destination sheet).

Run the macro (press F5 in the VBA Editor, or assign it to a button in Excel) and it will copy the correct rows automatically.

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