VBA复制工作表至当前工作簿后VLOOKUP引用异常求助

问题原因 #

1. 代码逻辑错误：你使用Workbooks.Add(fileStr)是基于指定模板创建新工作簿，而非直接打开目标关闭工作簿，导致复制的工作表来自新建的副本工作簿（名称自动加后缀1），公式自然会指向这个临时工作簿。
2. Excel默认行为：跨工作簿复制工作表时，Excel会自动将公式中的内部引用转换为外部引用，保留原工作簿的路径/名称标识。

解决方案 #

步骤1：修正打开工作簿的代码 #

将Workbooks.Add(fileStr)替换为Workbooks.Open(fileStr)，直接打开目标关闭工作簿，而非创建模板副本：

Dim wbk1 As Workbook, wbk2 As Workbook
Dim fileStr As String

fileStr = "C:\Users\Name\Documents\Template\EQ\Closed Workbook.xlsm"

Set wbk1 = ActiveWorkbook
' 修正：直接打开目标工作簿，而非基于模板新建
Set wbk2 = Workbooks.Open(fileStr)

wbk2.Sheets("A").Copy Before:=wbk1.Sheets(1)
wbk2.Sheets("B").Copy Before:=wbk1.Sheets(1)
wbk2.Sheets("C").Copy Before:=wbk1.Sheets(1)
wbk2.Sheets("D").Copy Before:=wbk1.Sheets(1)

' 关闭原工作簿，不保存（因为只是复制，不需要修改原文件）
wbk2.Close SaveChanges:=False

步骤2：批量移除公式中的外部工作簿引用 #

如果当前工作簿中已经存在EQ LIST工作表，复制完成后，遍历新复制的工作表，替换公式中的外部工作簿标识：

Dim wbk1 As Workbook, wbk2 As Workbook
Dim fileStr As String
Dim newSheet As Worksheet
Dim oldRef As String

fileStr = "C:\Users\Name\Documents\Template\EQ\Closed Workbook.xlsm"

Set wbk1 = ActiveWorkbook
Set wbk2 = Workbooks.Open(fileStr)

' 批量复制工作表，简化代码
wbk2.Sheets(Array("A", "B", "C", "D")).Copy Before:=wbk1.Sheets(1)

' 定义需要替换的外部引用字符串（匹配原工作簿名称格式）
oldRef = "'[" & wbk2.Name & "]"

' 遍历新复制的工作表，替换公式中的外部引用
For Each newSheet In wbk1.Sheets(1 To 4)
    newSheet.UsedRange.Replace What:=oldRef, Replacement:="'", LookAt:=xlPart
Next newSheet

wbk2.Close SaveChanges:=False

替代方案：复制值和格式后重新写入公式 #

如果不需要保留原公式的编辑历史，可先复制值和格式，再批量写入目标公式：

Dim wbk1 As Workbook, wbk2 As Workbook
Dim fileStr As String
Dim srcSheet As Worksheet, newSheet As Worksheet

fileStr = "C:\Users\Name\Documents\Template\EQ\Closed Workbook.xlsm"

Set wbk1 = ActiveWorkbook
Set wbk2 = Workbooks.Open(fileStr)

For Each srcSheet In wbk2.Sheets(Array("A", "B", "C", "D"))
    ' 在当前工作簿新建工作表
    Set newSheet = wbk1.Sheets.Add(Before:=wbk1.Sheets(1))
    newSheet.Name = srcSheet.Name
    ' 复制值和格式
    srcSheet.UsedRange.Copy
    newSheet.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    newSheet.Range("A1").PasteSpecial xlPasteFormats
    ' 批量写入目标公式（根据实际公式所在列调整范围）
    newSheet.Range("C:C").Formula = "=IFERROR(VLOOKUP(B1,'EQ LIST'!A:B,2,FALSE),"" "")"
Next srcSheet

wbk2.Close SaveChanges:=False
Application.CutCopyMode = False

关键说明 #

• 替换外部引用时，确保oldRef的格式与公式中的实际引用完全匹配（注意单引号、方括号的位置）。
• 如果当前工作簿没有EQ LIST工作表，需要先将该表从原工作簿复制过来，否则公式会返回错误值。

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