为何Excel VBA代码修改整个表格引用而非仅新增行？

问题原因 #

你的Rankings表格的A、B列被Excel自动识别为计算列。Excel表格的计算列特性会自动将新行输入的公式同步到整列所有单元格，导致每次新增工作表时，所有现有行的引用都被替换成新工作表的单元格地址。

修复方案 #

方案1：临时禁用表格自动扩展（保留动态引用） #

修改VBA代码，在添加新行并设置公式前，临时关闭表格的自动扩展功能，避免公式同步到整列：

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim RankingsTable As ListObject
    Dim NewRow As ListRow
    Dim originalAutoExpand As Boolean

    ' 引用Dashboard工作表中的Rankings表格
    On Error Resume Next
    Set RankingsTable = ThisWorkbook.Sheets("Dashboard").ListObjects("Rankings")
    On Error GoTo 0

    If Sh.Name <> "Dashboard" Then
        If Not RankingsTable Is Nothing Then
            ' 保存原始自动扩展设置
            originalAutoExpand = RankingsTable.AutoExpandListRange
            ' 禁用自动扩展，防止公式同步到整列
            RankingsTable.AutoExpandListRange = False
            
            Set NewRow = RankingsTable.ListRows.Add
            ' 为新行设置动态引用公式
            NewRow.Range.Cells(1, 1).Formula = "='" & Sh.Name & "'!R5C3"
            NewRow.Range.Cells(1, 2).Formula = "='" & Sh.Name & "'!R30C3"
            
            ' 恢复原始自动扩展设置
            RankingsTable.AutoExpandListRange = originalAutoExpand
        End If
    End If
End Sub

方案2：写入静态值（无需动态同步） #

如果不需要后续同步新工作表的数据变化，直接将目标单元格的当前值写入表格新行：

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim RankingsTable As ListObject
    Dim NewRow As ListRow

    On Error Resume Next
    Set RankingsTable = ThisWorkbook.Sheets("Dashboard").ListObjects("Rankings")
    On Error GoTo 0

    If Sh.Name <> "Dashboard" Then
        If Not RankingsTable Is Nothing Then
            Set NewRow = RankingsTable.ListRows.Add
            ' 写入静态值，而非公式
            NewRow.Range.Cells(1, 1).Value = Sh.Range("C5").Value
            NewRow.Range.Cells(1, 2).Value = Sh.Range("C30").Value
        End If
    End If
End Sub

方案3：手动取消计算列设置 #

如果不需要计算列特性，可手动取消：

1. 选中Rankings表格的A、B列
2. 右键点击→选择「表格」→「取消计算列」
3. 之后新增行的公式将不会同步到整列

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