跨工作表复制粘贴宏开发求助：需实现指定列复制而非整行

Hey there! Let's tweak your VBA code to copy only the specific columns you need (D, H, I, K, L) instead of the entire row. Here's how to do it, with explanations along the way:

Modified Code #

Sub CopySpecificColumns()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim bottomL As Long
    Dim c As Range
    Dim targetRow As Long
    
    ' Set references to your worksheets (cleaner, faster, and easier to adjust)
    Set sourceSheet = ThisWorkbook.Sheets("Cash Transactions RBS December")
    Set targetSheet = ThisWorkbook.Sheets("Recon")
    
    ' Find the last row with data in column A of the source sheet
    bottomL = sourceSheet.Range("A" & sourceSheet.Rows.Count).End(xlUp).Row
    
    ' Loop through each cell in column A of the source sheet
    For Each c In sourceSheet.Range("A1:A" & bottomL)
        ' Check if the cell matches your target value (note: you mentioned this should reference cell H? See note below!)
        If c.Value = "M1 GP LtdEUR" Then
            ' Get the next empty row in the target sheet
            targetRow = targetSheet.Range("A" & targetSheet.Rows.Count).End(xlUp).Row + 1
            
            ' Copy specific columns from the matching row to the target sheet
            ' Map source columns to target columns (adjust target column numbers if needed)
            sourceSheet.Cells(c.Row, 4).Copy targetSheet.Cells(targetRow, 1) ' D -> A
            sourceSheet.Cells(c.Row, 8).Copy targetSheet.Cells(targetRow, 2) ' H -> B
            sourceSheet.Cells(c.Row, 9).Copy targetSheet.Cells(targetRow, 3) ' I -> C
            sourceSheet.Cells(c.Row, 11).Copy targetSheet.Cells(targetRow, 4) ' K -> D
            sourceSheet.Cells(c.Row, 12).Copy targetSheet.Cells(targetRow, 5) ' L -> E
            
            ' Optional: For faster performance (no formatting copy), use direct value assignment instead:
            ' targetSheet.Cells(targetRow, 1).Value = sourceSheet.Cells(c.Row, 4).Value
            ' targetSheet.Cells(targetRow, 2).Value = sourceSheet.Cells(c.Row, 8).Value
            ' targetSheet.Cells(targetRow, 3).Value = sourceSheet.Cells(c.Row, 9).Value
            ' targetSheet.Cells(targetRow, 4).Value = sourceSheet.Cells(c.Row, 11).Value
            ' targetSheet.Cells(targetRow, 5).Value = sourceSheet.Cells(c.Row, 12).Value
        End If
    Next c
End Sub

Key Changes & Notes #

• Worksheet References: Using variables (sourceSheet, targetSheet) makes the code easier to read and modify—no need to repeat long sheet names everywhere.
• Target Column Mapping: We use Cells(row, columnNumber) to pinpoint exactly which columns to copy. For example, column D is the 4th column, so sourceSheet.Cells(c.Row, 4) targets that cell in the matching row.
• Empty Row Detection: targetRow finds the next blank row in the Recon sheet so we don't overwrite existing data.
• Condition Adjustment: You mentioned the check "should be based on name in cell value H"—if that means you need to check column H instead of column A for the match, replace If c.Value = "M1 GP LtdEUR" with:

  If c.Offset(0, 7).Value = "M1 GP LtdEUR" ' Offset 7 columns right from A to get to H
  

• Performance Tip: If you don't need to copy cell formatting (just the values), use the optional direct value assignment instead of Copy—it runs much faster for large datasets.

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