跨工作簿多列复制粘贴VBA报错‘下标越界’求排查

我将原本用于同工作簿单元格复制的VBA代码修改后，试图实现跨工作簿多列复制粘贴，但持续出现“Script out of range”（下标越界）错误。待复制的工作簿可能存在筛选区域，复制筛选区域或全部数据均可，尝试过取消筛选、移除With/End With结构但均未解决问题，请求排查代码错误：

Dim wbCopy As Workbook
Dim wbPaste As Workbook

On Error Resume Next
    Workbooks("Shipping.xlsx").Close SaveChanges:=False
On Error GoTo 0

Set wbCopy = Workbooks.Open("Q:\Test\Shipping.xlsx", UpdateLinks:=3)
Set wbCopy = wbCopy.Worksheets("List")
Set wbPaste = ThisWorkbook

With wbPaste.Worksheets("Shipping")
    .Columns("A").Value = wbCopy.Columns("C").Value
    .Columns("B").Value = wbCopy.Columns("D").Value
    .Columns("C").Value = wbCopy.Columns("E").Value
    .Columns("D").Value = wbCopy.Columns("F").Value
    .Columns("E").Value = wbCopy.Columns("N").Value
    .Columns("F").Value = wbCopy.Columns("O").Value
    .Columns("G").Value = wbCopy.Columns("P").Value
    .Columns("H").Value = wbCopy.Columns("Q").Value
    .Columns("I").Value = wbCopy.Columns("S").Value
End With

wbCopy.Close SaveChanges:=False

错误原因分析 #

• 变量类型混淆：一开始定义wbCopy为Workbook类型，但后续将其赋值为工作表对象wbCopy.Worksheets("List")，导致变量类型不匹配。后续调用wbCopy.Close时，wbCopy已变为工作表对象，没有Close方法，直接触发下标越界错误。
• 工作表名称或路径错误：如果Shipping.xlsx中不存在名为List的工作表，或当前工作簿中不存在Shipping工作表，也会触发下标越界错误。
• 筛选状态（非直接错误原因）：源工作表的筛选状态不会直接导致下标越界，但复制整列时会包含隐藏行，若需仅复制可见数据可额外处理。

修正后的代码 #

Dim wbCopy As Workbook
Dim wsCopy As Worksheet ' 单独定义工作表变量，避免类型混淆
Dim wbPaste As Workbook

' 关闭已打开的目标文件，避免重名冲突
On Error Resume Next
    Workbooks("Shipping.xlsx").Close SaveChanges:=False
On Error GoTo 0

' 打开源工作簿并指定工作表
Set wbCopy = Workbooks.Open("Q:\Test\Shipping.xlsx", UpdateLinks:=3)
Set wsCopy = wbCopy.Worksheets("List")
Set wbPaste = ThisWorkbook

' 可选：取消源工作表的筛选，复制全部数据
If wsCopy.AutoFilterMode Then
    wsCopy.AutoFilterMode = False
End If

' 批量复制对应列数据
With wbPaste.Worksheets("Shipping")
    .Columns("A").Value = wsCopy.Columns("C").Value
    .Columns("B").Value = wsCopy.Columns("D").Value
    .Columns("C").Value = wsCopy.Columns("E").Value
    .Columns("D").Value = wsCopy.Columns("F").Value
    .Columns("E").Value = wsCopy.Columns("N").Value
    .Columns("F").Value = wsCopy.Columns("O").Value
    .Columns("G").Value = wsCopy.Columns("P").Value
    .Columns("H").Value = wsCopy.Columns("Q").Value
    .Columns("I").Value = wsCopy.Columns("S").Value
End With

' 关闭源工作簿
wbCopy.Close SaveChanges:=False

' 释放对象变量，避免内存泄漏
Set wsCopy = Nothing
Set wbCopy = Nothing
Set wbPaste = Nothing

修正说明 #

• 新增wsCopy工作表变量，彻底解决工作簿与工作表对象混淆的问题，这是修复当前错误的核心。
• 保留关闭已打开文件的逻辑，避免文件被占用导致打开失败。
• 增加可选的取消筛选代码，若需保留筛选状态可注释掉该段。
• 最后添加对象变量释放语句，优化内存使用。

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