如何用公式或VBA实现Excel跨工作簿工作表复制？含报错解决

一、VBA代码错误修复 #

你遇到的“下标越界”错误，核心原因是打开源工作簿后，ActiveWorkbook自动切换为AAA.xlsx，而目标工作表bbb在固定的BBB.xlsx中，导致代码无法定位到目标工作表。以下是修复后的代码：

Option Explicit ' 强制变量声明，避免隐性错误

Sub CopyPaste()
    Dim sourceWb As Workbook
    Dim sourceWs As Worksheet
    Dim targetWs As Worksheet
    Dim endRow As Long
    
    ' 明确指定目标工作簿（假设代码存放在BBB.xlsx中，用ThisWorkbook指代自身）
    Set targetWs = ThisWorkbook.Sheets("bbb")
    
    ' 打开源工作簿
    Set sourceWb = Workbooks.Open("Z:\AAA.xlsx")
    Set sourceWs = sourceWb.Sheets("aaa")
    
    Application.ScreenUpdating = False ' 关闭屏幕刷新，提升运行速度
    
    ' 获取目标工作表的最后一行，避免覆盖已有数据
    endRow = targetWs.Range("A" & targetWs.Rows.Count).End(xlUp).Row
    
    ' 复制源数据值到目标工作表的下一行
    sourceWs.Range("A1:P250000").Copy
    targetWs.Range("A" & endRow + 1).PasteSpecial Paste:=xlPasteValues
    
    ' 清理复制模式，释放剪贴板
    Application.CutCopyMode = False
    
    ' 清空源工作表数据（按需保留）
    sourceWs.Range("A1:P250000").ClearContents
    
    ' 关闭源工作簿，控制是否保存修改
    sourceWb.Close SaveChanges:=True ' 无需保存则改为False
    
    Application.ScreenUpdating = True ' 恢复屏幕刷新
End Sub

关键修改说明： #

• 用ThisWorkbook替代ActiveWorkbook，明确指向存放代码的目标工作簿，避免ActiveWorkbook切换导致的引用错误
• 增加Option Explicit强制变量声明，提前排查变量拼写类错误
• 移除不必要的Activate操作，直接通过对象引用操作工作表，稳定性更强
• 新增源工作簿关闭逻辑，避免文件残留打开状态

如果需要每月更换源文件路径，可将路径提取为单元格参数（比如在BBB的Sheet1!A1存放路径），代码中读取：

Dim sourcePath As String
sourcePath = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
Set sourceWb = Workbooks.Open(sourcePath)

二、无需VBA的实现方法 #

方案1：Power Query（推荐，支持关闭源文件更新） #

Power Query可实现自动化数据提取，无需打开源文件，步骤如下：

1. 打开BBB.xlsx，切换到「数据」选项卡
2. 点击「获取数据」→「从文件」→「从工作簿」，选择源文件AAA.xlsx
3. 在导航器中选择「aaa」工作表，点击「加载到」，选择加载到「现有工作表」的bbb工作表指定位置（比如A1）
4. 后续更新时，点击「数据」选项卡的「全部刷新」即可；若源文件路径变更，右键查询→「编辑」，在高级编辑器中修改文件路径

方案2：公式法（需保持源文件打开） #

使用INDEX+INDIRECT组合公式，在BBB的bbb工作表中输入：

=INDEX('[AAA.xlsx]aaa'!$A:$P,ROW(),COLUMN())

下拉填充到所需行和列即可。若更换源文件，直接替换公式中的[AAA.xlsx]部分，但此方法要求源文件必须处于打开状态，否则公式会返回错误。

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