Excel VBA多工作表值复制异常：仅粘贴最后工作表数据

问题根源 #

1. 工作表指定错误：需求要求将数据粘贴到输出工作簿的Report工作表，但代码中错误地将输出表设为Sheet1，且粘贴操作仍直接引用Sheet1，导致数据未写入目标工作表。
2. 粘贴起始位置逻辑错误：
   • 当输出表B列无前置数据时，outSht.Cells(outSht.Rows.Count, "B").End(xlUp).Row会返回1（Excel默认空列的最后非空行是第1行），计算出的lastrow2为2，此时Range("B9:B" & lastrow2)会变成无效的反向范围，Excel会自动调整为从B2开始粘贴，导致位置错误或覆盖之前内容。
   • 粘贴范围写法冗余：无需指定结束行，只需指定起始单元格，粘贴时Excel会自动匹配复制区域的行数。

修正后的代码 #

Sub CopyDataToReport()
    Dim reference As String
    Dim ws As Worksheet, outSht As Worksheet
    Dim wb As Workbook
    Dim lastrow1 As Long, lastrow2 As Long

    ' 获取参考工作簿路径（从当前工作簿Sheet1的B4单元格）
    reference = ThisWorkbook.Sheets("Sheet1").Cells(4, 2).Value

    ' 指定输出目标为Report工作表（修正原代码的Sheet1错误）
    Set outSht = ThisWorkbook.Sheets("Report")

    ' 打开参考工作簿
    Set wb = Workbooks.Open(reference)

    Application.ScreenUpdating = False

    ' 遍历参考工作簿的所有工作表
    For Each ws In wb.Worksheets
        ' 跳过Sheet1至Sheet4
        If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" And ws.Name <> "Sheet3" And ws.Name <> "Sheet4" Then
            ' 获取当前参考工作表A列的最后一行（从A12开始复制）
            lastrow1 = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            
            ' 计算输出工作表B列的粘贴起始行：如果B9及以上无数据，从B9开始；否则从最后非空行的下一行开始
            lastrow2 = outSht.Cells(outSht.Rows.Count, "B").End(xlUp).Row
            If lastrow2 < 9 Then
                lastrow2 = 9
            Else
                lastrow2 = lastrow2 + 1
            End If

            ' 复制参考表A12至最后一行的数据
            ws.Range("A12:A" & lastrow1).Copy
            
            ' 粘贴到输出表的指定起始位置（仅指定起始单元格，自动匹配行数）
            outSht.Range("B" & lastrow2).PasteSpecial Paste:=xlPasteValues
        End If
    Next ws

    Application.CutCopyMode = False ' 清除剪贴板状态
    Application.ScreenUpdating = True
    wb.Close SaveChanges:=False ' 关闭参考工作簿，不保存修改
End Sub

关键修改说明 #

• 修正输出工作表为Report，统一使用outSht变量引用，避免重复指定错误工作表。
• 优化粘贴起始行的计算逻辑：确保初始状态下从B9开始粘贴，后续从已有数据的下一行开始，避免反向范围错误。
• 简化粘贴范围：仅指定起始单元格B" & lastrow2，让Excel自动处理粘贴区域的扩展，避免范围匹配错误。
• 添加wb.Close SaveChanges:=False关闭参考工作簿，避免手动关闭；添加Application.CutCopyMode = False清除剪贴板，释放资源。

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