VBA代码将Sorts工作表数据复制到归档表时出错求助

核心错误原因 #

执行wsDest.Paste Destination:=("B" & lDestLastRow)时出错，是因为Paste方法的Destination参数要求传入Range对象，但你传入的是字符串格式的单元格地址，VBA无法解析该格式。

代码修正步骤 #

1. 修复粘贴语句 #

将错误的粘贴代码替换为：

wsDest.Paste Destination:=wsDest.Range("B" & lDestLastRow)

或者用更简洁的写法（直接在Copy方法中指定目标，无需单独调用Paste）：

wsCopy.Range(wsCopy.Cells(2, 2), wsCopy.Cells(lCopyLastRow, lastCol)).Copy wsDest.Range("B" & lDestLastRow)

2. 修正复制区域最后一行的判断逻辑 #

你当前用wsCopy.Cells(wsCopy.Rows.Count, 1).End(xlUp).Row基于A列找最后一行，但实际数据从B2开始，应该基于B列判断，避免A列无数据时导致复制范围错误：

lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, 2).End(xlUp).Row

3. 可选：优化复制方式（避免剪贴板） #

如果不需要复制格式，仅复制数据，可直接赋值，效率更高：

' 获取复制区域的行数和列数
Dim copyRows As Long, copyCols As Long
copyRows = lCopyLastRow - 1 ' 从第2行开始，总行数=最后行-1
copyCols = lastCol - 1 ' 从第2列开始，总列数=最后列-1

' 直接赋值数据
wsDest.Range("B" & lDestLastRow).Resize(copyRows, copyCols).Value = wsCopy.Range(wsCopy.Cells(2, 2), wsCopy.Cells(lCopyLastRow, lastCol)).Value

完整修正后的代码 #

Sub EOS_Archive()

' Copy currrent CPT loads to archive area

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lastCol As Long
Dim lDestLastRow As Long

    Set wsCopy = Workbooks("Dock Door Mover 2.xlsm").Worksheets("Sorts")
    Set wsDest = Workbooks("Dock Door Mover 2.xlsm").Worksheets("Sorts Archive")

    '1. Find last used row in the copy range based on data in column B
    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, 2).End(xlUp).Row

    ' Get the last cell with data in row 1
    lastCol = wsCopy.Cells(1, wsCopy.Columns.Count).End(xlToLeft).Column

    '2. Find first blank row in the destination range based on data in column B
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row

    '3. Copy & Paste Data（简洁写法）
    wsCopy.Range(wsCopy.Cells(2, 2), wsCopy.Cells(lCopyLastRow, lastCol)).Copy wsDest.Range("B" & lDestLastRow)
    
    ' 若仅需复制数据，注释上面一行，启用下面的代码：
    ' Dim copyRows As Long, copyCols As Long
    ' copyRows = lCopyLastRow - 1
    ' copyCols = lastCol - 1
    ' wsDest.Range("B" & lDestLastRow).Resize(copyRows, copyCols).Value = wsCopy.Range(wsCopy.Cells(2, 2), wsCopy.Cells(lCopyLastRow, lastCol)).Value
    
End Sub

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