如何在 Excel 新建工作表时自动向汇总表添加引用行？

2026-11-04数据小陈

可以。在支持 VBA 的桌面版 Excel 中，可以通过工作簿的 Workbook_NewSheet 事件，在新建工作表时自动向汇总表追加一行，并写入引用该工作表的公式，无需点击按钮。

VBA 负责在新建工作表时添加汇总记录，公式负责持续更新产品数据。产品工作表不必采用连续编号，只要代码使用实际的工作表名称生成引用即可。

准备汇总表和产品布局

以下示例中的工作表名称和单元格位置可以按实际情况修改：

汇总工作表名为 Summary，第 1 行是表头。
汇总表 A、B、C 列分别记录工作表名称、产品名称、单价。
每张产品工作表的 B2 存放产品名称，B3 存放单价。
汇总表 A 列用于判断已有记录的最后一行，数据区域下方不放其他内容。

这里的汇总表使用普通单元格区域。代码会向下一行写入数据和公式，不需要先创建 Excel Table。

在 ThisWorkbook 中添加事件代码

事件过程需要放在当前工作簿的 ThisWorkbook 中。先创建 Summary 工作表，再打开 VBA 编辑器，在项目中双击 ThisWorkbook，粘贴以下代码：

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim summary As Worksheet
    Dim nextRow As Long
    Dim source As String

    If Not TypeOf Sh Is Worksheet Then Exit Sub
    If Sh.Name = "Summary" Then Exit Sub

    On Error GoTo HandleError
    Application.EnableEvents = False

    Set summary = Me.Worksheets("Summary")
    nextRow = summary.Cells(summary.Rows.Count, "A").End(xlUp).Row + 1

    source = "'" & Replace(Sh.Name, "'", "''") & "'!"

    With summary
        .Cells(nextRow, 1).Value = Sh.Name
        .Cells(nextRow, 2).Formula = "=" & source & "$B$2"
        .Cells(nextRow, 3).Formula = "=" & source & "$B$3"
    End With

CleanExit:
    Application.EnableEvents = True
    Exit Sub

HandleError:
    MsgBox "Unable to update Summary: " & Err.Description, vbExclamation
    Resume CleanExit
End Sub

将工作簿保存为支持宏的格式，例如 .xlsm，并在打开时允许宏运行。

代码暂时关闭事件，避免写入汇总表时触发其他事件过程；出错后也会恢复事件设置。

新建工作表后会发生什么

假设新建的工作表名为 Sheet2，代码会在汇总表的下一行写入该名称，并生成以下公式：

='Sheet2'!$B$2
='Sheet2'!$B$3

之后在 Sheet2 的 B2、B3 中填写或修改产品数据时，汇总表中对应的单元格会随公式计算更新。如果工作簿处于手动计算模式，需要重新计算。

代码会在工作表名称两侧加上单引号，并转义名称中已有的单引号，以支持包含空格或单引号的名称。

使用时需要注意什么

新建工作表时，产品内容可能尚未填写。事件会先创建引用公式，之后再填写产品数据即可。上述直接引用公式在源单元格为空时通常显示 0。如果希望空白保持为空白，可以使用这样的公式：

=IF('Sheet2'!$B$2="","",'Sheet2'!$B$2)

示例会将所有新建的普通工作表视为产品表。如果还要新增备注表或统计表，需要增加识别条件。不要直接依赖新建后才手动设置的名称前缀：Workbook_NewSheet 触发时，用户通常还没有完成重命名。若要统一命名、复制模板并区分产品表，可以另写一个专门的“新增产品”宏来完成这些步骤。

后续重命名时，公式引用与名称文字的行为不同。在 Excel 中正常重命名产品工作表后，直接引用它的公式会随之调整；A 列通过 .Value 写入的工作表名称是普通文字，不会自动变化。删除产品工作表后，相关公式会出现 #REF!，这段代码不会自动删除汇总行。

这段事件代码只处理安装代码后新增的工作表，已有产品工作表需要另行遍历补齐。Excel 网页版不能运行这段 VBA，需要在支持宏的桌面版 Excel 中使用。