Excel VBA：复制粘贴公式时如何避免单元格引用递增并更新工作表引用？

这个需求我经常碰到，核心就是要让公式里的单元格坐标（比如O40）固定不变，但工作表名称能自动切换成目标单元格所在的表名。下面分手动操作和VBA代码两种场景给你解决办法：

一、手动操作（不用写代码） #

1. 绝对引用+批量替换 #

这是最直接的手动方法：

• 先把原公式里的单元格引用改成绝对引用：把=MID('Sheet1'!O40,1,5)改成=MID('Sheet1'!$O$40,1,5)（加上$符号锁定行和列）。
• 复制这个公式到目标单元格后，按Ctrl+H打开「查找和替换」对话框，查找内容填'Sheet1'!，替换为'Sheet2'!，点击「全部替换」就能批量切换工作表名。

2. 动态引用工作表名（自动适配） #

如果不想每次手动替换工作表名，可以用INDIRECT函数让公式自动识别当前工作表：

=MID(INDIRECT("'"&RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))&"'!$O$40"),1,5)

这个公式会自动提取当前单元格所在的工作表名称，不管你复制到哪个工作表，都会自动使用该表的O40单元格，而且$O$40确保单元格引用不会递增。

二、VBA代码实现（批量处理更高效） #

你的测试代码里单元格引用递增，是因为默认用的是相对引用。下面几个代码方案可以解决问题：

1. 先转绝对引用，再复制替换 #

先把原单元格的公式转为绝对引用，复制后再替换工作表名：

Sub CopyFormulaWithFixedRef()
    ' 把E2的公式转为绝对引用格式
    Range("E2").Formula = Range("E2").FormulaR1C1
    ' 复制公式到E126
    Range("E2").Copy
    Range("E126").PasteSpecial xlPasteFormulas
    ' 替换工作表名称为Sheet2
    Range("E126").Formula = Replace(Range("E126").Formula, "'Sheet1'!", "'Sheet2'!")
    ' 取消复制状态
    Application.CutCopyMode = False
End Sub

2. 直接构建目标公式（更高效） #

不需要复制粘贴，直接给目标单元格赋值拼接好的公式，完全避免引用递增问题：

Sub BuildFormulaDirectly()
    ' 提取原公式中工作表名之后的部分（比如"O40,1,5)"）
    Dim formulaPart As String
    formulaPart = Mid(Range("E2").Formula, InStr(Range("E2").Formula, "!") + 1)
    ' 给目标单元格设置新公式，指定Sheet2和固定的单元格引用
    Range("E126").Formula = "=MID('Sheet2'!" & formulaPart
End Sub

3. 绝对引用+粘贴公式 #

如果坚持用复制粘贴的逻辑，先确保原公式是绝对引用，再粘贴后替换工作表名：

Sub CopyWithFixedRef()
    ' 把原公式里的O40改成绝对引用$O$40
    Range("E2").Formula = Replace(Range("E2").Formula, "O40", "$O$40")
    ' 复制粘贴公式
    Range("E2").Copy
    Range("E126").PasteSpecial xlPasteFormulas
    ' 替换工作表名
    Range("E126").Formula = Replace(Range("E126").Formula, "'Sheet1'!", "'Sheet2'!")
    Application.CutCopyMode = False
End Sub

选哪个方法看你的具体场景：如果是偶尔操作，手动方法足够；如果是批量处理大量公式，VBA代码会更省心。

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