复制工作表时更新单元格公式引用

Question

我可以翻译此内容。这是一段关于编程的文本，讲述了一个工作簿中有5个表格，当手动复制其中两个表格时，单元格公式中的引用会自动更改。例如，在某些单元格中，我有以下公式：

=IF('1_Result'!B5="";NA();'1_Result'!B5*$C$3)

在我复制了新的工作表之后，新工作表中的公式会发生变化:

=IF('1_Result (2)'!B5="";NA();'1_Result (2)'!B5*$C$3)

"1_Result" 是原始表格，"1_Result (2)" 是复制的表格。当我使用以下代码进行复制时：

t1.Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = nshn & "_Table"

复制的工作表中的公式不会更改引用，因此它保持不变，例如：

=IF('1_Result'!B5="";NA();'1_Result'!B5*$C$3)

我该如何修改代码，以便更改引用？

- Trenera

---

有趣的问题，点赞。似乎“复制”方法并不像手动复制工作表那样做相同的事情。 - David Zemens

你是什么意思说“我手动复制了其中两个”？是同时复制吗？如果是的话，可以尝试在VBA中使用相同的方法：Sheets(Array("Sheet1", "1_Result")).Copy After:=Sheets(Sheets.Count) - 这对我有效：初始时，Sheet1的公式为='1_Result'!B5，在复制这些工作表后，Sheet1 (2)的公式变为='1_Result (2)'!B5。如果有帮助，请告诉我。 - Dmitry Pavliv

我相信你是对的。引用没有更新的原因是，我一个一个地复制它们，而不是同时复制。我现在会去看一下。 - Trenera

Answer 1

正如我在评论中提到的，如果您想要更新引用，请同时复制两个工作表：

Sheets(Array("Sheet1", "1_Result")).Copy After:=Sheets(Sheets.Count)

对我而言它起作用：

Sheet1 最初的公式为 ='1_Result'!B5，在复制这些表格后，Sheet1 (2) 的公式变为 ='1_Result (2)'!B5

- Dmitry Pavliv

Answer 2

正如 simco 所指出的，我没有一次性将表格复制在一起，而是逐个复制。 以下代码解决了这个问题：

Sheets(Array("1_Table", "1_Result")).Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count - 1).Name = nshn & "_Table"
Sheets(Sheets.Count).Name = nshn & "_Result"

- Trenera

Answer 3

在试图找出问题所在时，我发现了一种替代方案，虽然不建议使用，但可能对某些人有帮助。我使用了一个函数，在单个工作表中将一个字符串替换为另一个字符串。以下是代码：

Function FormulaFindAndReplace(sh As Worksheet, phrase As String, replacement As String)
With sh
    Set Found_Link = Cells.Find(what:=phrase, After:=ActiveCell, _
        LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, _
        searchdirection:=xlNext, MatchCase:=False)
    While UCase(TypeName(Found_Link)) <> UCase("Nothing")
       Found_Link.Activate
       Found_Link.Formula = Replace(Found_Link.Formula, phrase, replacement)
       Set Found_Link = Cells.FindNext(After:=ActiveCell)
    Wend
End With
End Function

- Trenera