将源工作簿多列按指定顺序批量复制到目标工作簿末行下方

Got it, let's fix that column misalignment issue you're facing with your VBA code. The root problem here is that your original code calculates the starting paste row for each target column individually. If those target columns have different numbers of existing rows, each dataset gets pasted to a different starting position, causing the shift you're seeing.

The solution is to calculate a single, consistent starting row on the target worksheet first, then paste all your source columns to that same starting row in their respective target columns. Here's the revised, fixed code:

Sub CopyCoverage()
    Dim x As Worksheet, y As Worksheet
    Dim SourceLastRow As Long, DestStartRow As Long
    
    ' Set references to your source and target worksheets
    Set x = Workbooks("Input.xlsb").Worksheets("Opportunity")
    Set y = ThisWorkbook.Worksheets("Lapsed Opps")
    
    ' Get the last row with data in the source sheet
    SourceLastRow = x.Cells.SpecialCells(xlCellTypeLastCell).Row
    
    ' Calculate the FIRST empty row in the target sheet
    ' Using column A as the reference (adjust to a column that always has data if needed)
    DestStartRow = y.Cells(y.Rows.Count, "A").End(xlUp).Row + 1
    
    ' Paste each source range to the fixed starting row in their target columns
    x.Range("G2:G" & SourceLastRow).Copy y.Cells(DestStartRow, "D")
    x.Range("I2:I" & SourceLastRow).Copy y.Cells(DestStartRow, "M")
    x.Range("P2:P" & SourceLastRow).Copy y.Cells(DestStartRow, "A")
    x.Range("Y2:Y" & SourceLastRow).Copy y.Cells(DestStartRow, "C")
    x.Range("Z2:Z" & SourceLastRow).Copy y.Cells(DestStartRow, "B")
    x.Range("AJ2:AJ" & SourceLastRow).Copy y.Cells(DestStartRow, "G")
    x.Range("AK2:AK" & SourceLastRow).Copy y.Cells(DestStartRow, "H")
    x.Range("AL2:AL" & SourceLastRow).Copy y.Cells(DestStartRow, "I")
    x.Range("AM2:AM" & SourceLastRow).Copy y.Cells(DestStartRow, "J")
    x.Range("EC2:EC" & SourceLastRow).Copy y.Cells(DestStartRow, "F")
    x.Range("EG2:EG" & SourceLastRow).Copy y.Cells(DestStartRow, "E")
    
    ' Clear the copy mode to free up memory
    Application.CutCopyMode = False
End Sub

Key Fixes & Explanations: #

• Consistent Starting Row: DestStartRow is calculated once at the beginning, using column A as the reference point (swap this to a different column if column A isn't guaranteed to have data in every row). This ensures all pasted data starts on the exact same row.
• Fixed Target Positions: Every paste operation now uses the pre-calculated DestStartRow instead of recalculating the last row for each individual column.
• Clarified Variable Names: Renamed LastRow to SourceLastRow to make it clear we're referring to the source sheet's last data row, avoiding confusion.
• Bug Correction: Fixed a typo in one of the original lines where LastRow was used instead of the declared variable.

This will make sure all your columns paste aligned perfectly below the last non-empty row in the target workbook.

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