同一工作簿跨工作表调用命名范围遇VBA运行时错误1004

问题背景 #

工作簿包含多张工作表，名称管理器中存在如下工作簿级命名范围：

主工作表Master有命名范围SizeHeader，引用为=Master!$E$7:$W$7。当修改Master表中指定单元格时，通过变量strCartonType（测试值为"PremierPeeled"）尝试将跨工作表的命名范围数据复制到SizeHeader，使用以下代码均触发错误：

Range("SizeHeader") = Range(strCartonType)

Range(strCartonType).Copy Range("SizeHeader")

错误提示：

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

问题原因 #

在工作表模块中直接使用Range()方法时，VBA默认以当前活动工作表作为父对象。由于PremierPeeled的引用目标位于BrandPeeledSizes工作表，而非当前的Master工作表，VBA无法在当前工作表中找到对应范围，因此触发错误。

解决方案 #

需要明确通过工作簿对象来引用跨工作表的命名范围，避免默认父对象的干扰，以下是两种可行写法：

1. 复制数据（含格式）

ThisWorkbook.Names(strCartonType).RefersToRange.Copy Destination:=Range("SizeHeader")

2. 仅赋值数据（不含格式，效率更高）

Range("SizeHeader").Value = ThisWorkbook.Names(strCartonType).RefersToRange.Value

额外检查项 #

• 确认PremierPeeled是工作簿级命名范围：打开名称管理器，查看该命名范围的“范围”选项是否为“工作簿”。如果是工作表级命名范围，需指定工作表前缀调用：

  Range("SizeHeader").Value = Worksheets("BrandPeeledSizes").Range(strCartonType).Value
  

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