Excel宏中如何引用Data工作表后的下一个动态命名工作表？

问题背景 #

• 主报表包含Data工作表，用于存储所有销售交易记录
• 零售团队会将外部的ISO交易工作表复制到主报表中，该表始终位于Data工作表的下一个位置，但工作表名称会随交易日期动态变更
• 手动核算时使用的公式：

  =IFERROR(IF(S2<0,VLOOKUP(P2&0,'ISO History 2.3'!U:V,2,0),0),0)
  

• 录制宏后生成的VBA代码硬编码了固定工作表名称，无法适配名称变更：

  =IFERROR(IF(RC[-2]<0,VLOOKUP(RC[-5]&0,'ISO History 2.3'!C:C[1],2,0),0),0)
  

解决方案 #

可以通过工作表的位置关系动态引用目标表，无需依赖可变的工作表名称，以下是两种实用方法：

方法1：VBA中直接构建动态公式 #

利用Sheets("Data").Next获取Data工作表的下一个工作表对象，再提取其名称拼接进公式：

Sub 批量写入佣金公式()
    Dim isoSheet As Worksheet
    Dim lastRow As Long
    
    ' 获取Data之后的目标工作表
    Set isoSheet = Sheets("Data").Next
    
    ' 获取Data表中S列的最后一行数据行号
    lastRow = Sheets("Data").Cells(Rows.Count, "S").End(xlUp).Row
    
    ' 给T列（假设是公式输出列）批量写入动态公式
    Sheets("Data").Range("T2:T" & lastRow).FormulaR1C1 = _
        "=IFERROR(IF(RC[-2]<0,VLOOKUP(RC[-5]&0,'" & isoSheet.Name & "'!C:C[1],2,0),0),0)"
End Sub

这种方式直接通过位置定位目标表，完全避开了名称变更的问题，只要目标表始终在Data之后就会生效。

方法2：定义动态名称（适配手动+宏场景） #

如果需要同时支持手动输入公式和宏操作，可以通过Excel名称管理器定义动态表名：

1. 点击「公式」选项卡 → 「名称管理器」→ 「新建」
2. 名称设为ISO_History_Table，引用位置输入：

   =INDEX(MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,255),MATCH("Data",MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,255),0)+1)
   

3. 手动公式可修改为：

   =IFERROR(IF(S2<0,VLOOKUP(P2&0,INDIRECT(ISO_History_Table&"!U:V"),2,0),0),0)
   

   宏中也可以直接调用这个名称，无需修改表名逻辑。

注意事项 #

• 确保Data工作表之后仅存在一个目标ISO工作表，否则Sheets("Data").Next会取第一个后续工作表
• 如果Data工作表的位置发生变动，只要目标表始终紧跟在它后面，位置引用逻辑依然有效

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