基于D列条件复制指定工作簿C、D列数据到当前工作簿的VBA问题

Let's build out your existing code to meet your specific needs: copying Account (Column C) and Transaction Count (Column D) data starting from row 13, only including rows where the transaction count is greater than 1, and excluding the final "Grand Total" row.

Complete VBA Code #

Private Sub CmdGetData_Click()
    Dim wb As Workbook, wb2 As Workbook
    Dim NewFile As Variant
    Dim ws As Worksheet, ws2 As Worksheet
    Dim lastRow As Long, targetRow As Long
    Dim i As Long
    
    ' Let user select the source workbook
    NewFile = Application.GetOpenFilename( _
        FileFilter:="Excel Files (*.xlsx; *.xls), *.xlsx; *.xls", _
        Title:="Select the Source Workbook")
    If NewFile = False Then Exit Sub ' Exit if user cancels selection
    
    ' Set reference to current workbook (where this code runs) and target worksheet
    Set wb = ThisWorkbook
    ' Replace "TargetSheet" with your actual target worksheet name
    Set ws = wb.Worksheets("TargetSheet")
    
    ' Open the selected source workbook
    Set wb2 = Workbooks.Open(NewFile)
    ' Replace "SourceSheet" with your actual source worksheet name
    Set ws2 = wb2.Worksheets("SourceSheet")
    
    ' Find the last row with data in Column C of the source sheet
    lastRow = ws2.Cells(ws2.Rows.Count, "C").End(xlUp).Row
    
    ' Start pasting data at row 2 of target sheet (adjust if your header is different)
    targetRow = 2
    
    ' Loop through rows starting from row 13
    For i = 13 To lastRow
        ' Skip the Grand Total row and only include rows with transaction count >1
        If ws2.Cells(i, "C").Value <> "Grand Total" And ws2.Cells(i, "D").Value > 1 Then
            ' Copy Account (Column C) to target Column A
            ws.Cells(targetRow, "A").Value = ws2.Cells(i, "C").Value
            ' Copy Transaction Count (Column D) to target Column B
            ws.Cells(targetRow, "B").Value = ws2.Cells(i, "D").Value
            targetRow = targetRow + 1 ' Move to next target row
        End If
    Next i
    
    ' Close the source workbook without saving changes
    wb2.Close SaveChanges:=False
    
    ' Notify user of completion
    MsgBox "Data extraction finished! Copied " & targetRow - 2 & " rows of data.", vbInformation
End Sub

Key Details & Customizations #

• Worksheet References: Replace "TargetSheet" and "SourceSheet" with your actual worksheet names to avoid relying on ActiveSheet (which can cause errors if the wrong sheet is active).
• Filter Logic: The condition checks two things:
  1. The cell in Column C is not "Grand Total" (adjust the column letter if your Grand Total is in a different column, e.g., ws2.Cells(i, "D") if it's in Column D)
  2. The transaction count in Column D is greater than 1
• Efficient Data Transfer: Using direct value assignment (ws.Cells(targetRow, "A").Value = ...) is faster and more reliable than copy-paste, as it avoids clipboard conflicts.
• Target Row Start: Adjust targetRow = 2 if your target sheet has a header in row 1 (or start at row 1 if there's no header).

Troubleshooting Tips #

• If your source data has blank rows between row 13 and the Grand Total, the loop will still handle them correctly since it checks each row individually.
• If the Grand Total row uses a different label (e.g., "Total"), update the string in the condition to match.

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