复制含命名区域的工作表后，同模块内无法找到新命名区域

我尝试在模块内（或点击按钮控件）复制包含**工作表级（sheet-level）**命名区域的工作表，并修改新工作表中部分命名区域的值。工作表复制环节正常，但复制得到的命名区域在代码运行结束前无法被找到，相关代码如下：

Private Sub MyCode()

  Dim newName As String

  On Error Resume Next
  newName = "sheet2"

  If newName <> "" Then
    Sheets("sheet1").Copy After:=Sheets("sheet1")
    On Error Resume Next
    ActiveSheet.Name = newName
  End If
  
  Range(newName + "!myRangeName") = newValue
End Sub

该命名区域为工作表级，且计算功能已启用。但分开运行以下两段代码则能正常完成工作表复制并修改命名区域的值：

第一段代码（复制并重命名工作表）：

Private Sub MyCode1()

  Dim newName As String

  On Error Resume Next
  newName = "sheet2"

  If newName <> "" Then
    Sheets("sheet1").Copy After:=Sheets("sheet1")
    On Error Resume Next
    ActiveSheet.Name = newName
  End If
End Sub

第二段代码（修改命名区域值）：

Private Sub MyCode2()
  Range("sheet2" + "!myRangeName") = newValue
End Sub

原因分析 #

Excel在执行工作表复制操作后，新生成的工作表级命名区域需要短暂时间完成系统注册。在同一段代码的连续执行上下文里，Excel的名称管理器还未同步更新这个新的命名区域，导致直接通过Range("工作表名!命名区域")无法定位到目标区域。另外代码中滥用On Error Resume Next会掩盖错误，让你无法直观发现“找不到区域”的问题。

解决方法 #

方法1：通过新工作表对象直接引用命名区域（推荐） #

不需要依赖工作表名称字符串，直接用复制后得到的工作表对象访问其命名区域，避免名称同步问题：

Private Sub MyCode()
  Dim newName As String
  Dim newSheet As Worksheet

  newName = "sheet2"
  ' 移除不必要的错误屏蔽，便于调试
  If newName <> "" Then
    Sheets("sheet1").Copy After:=Sheets("sheet1")
    Set newSheet = ActiveSheet
    newSheet.Name = newName
    ' 通过工作表对象直接访问自身的命名区域
    newSheet.Range("myRangeName") = newValue
  End If
End Sub

方法2：强制Excel刷新名称管理器 #

如果必须通过名称字符串引用，可在复制工作表后强制刷新所有计算和名称注册，但效率略低于方法1：

Private Sub MyCode()
  Dim newName As String

  newName = "sheet2"
  ' 改用错误捕获，便于排查问题
  On Error GoTo ErrorHandler
  If newName <> "" Then
    Sheets("sheet1").Copy After:=Sheets("sheet1")
    ActiveSheet.Name = newName
    ' 强制刷新所有计算和名称注册
    Application.CalculateFullRebuild
    ' 用&拼接字符串，避免+触发数值运算错误
    Range(newName & "!myRangeName") = newValue
  End If
Exit Sub
ErrorHandler:
  MsgBox "错误：" & Err.Description
End Sub

额外注意事项 #

• 不要滥用On Error Resume Next，它会隐藏代码中的错误，改用On Error GoTo处理异常更利于调试。
• VBA中拼接字符串优先用&而非+，+可能会触发数值运算，导致意外错误。

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