Excel VBA跨工作簿复制公式与数据时如何不生成外部链接

需要将名为old.xlsm的工作簿内的单元格内容复制粘贴到当前已打开的工作簿中，测试的两种复制方案均存在粘贴后附带源工作簿外部链接的问题：

• 第一种直接复制的写法，粘贴后内容自动链接到old.xlsm源工作簿：

Workbooks("Old.xlsm").Worksheets("Bookings").Cells.Copy ThisWorkbook.Worksheets("Bookings").Range ("A1")

• 第二种使用PasteSpecial粘贴公式的写法，虽然可以正常粘贴数据，但公式内会自动追加外部源路径，示例粘贴后的公式效果如下：

Workbooks("Old.xlsm").Worksheets("Bookings").Cells.Copy

ThisWorkbook.Sheets("Bookings").Range("A1").PasteSpecial Paste:=xlPasteFormulas, _ 
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

=INDEX('H:\01-Work\Flex & Time keeping\[old.xlsm]Flexi Planner'!C:C,$B12)

已知直接断开工作簿链接的方案只会保留单元格值，无法保留原有公式结构，需要实现粘贴内容到当前工作簿时不附带任何外部链接，同时保留公式本身的计算结构。

跨工作簿复制粘贴时Excel自动给公式追加外部源路径是默认机制，要保留公式结构同时移除外部链接，可直接使用以下两种方案，不会丢失格式或把公式转为静态值：

方案1：VBA批量替换公式标记（性能最优，推荐） #

不需要依赖剪贴板粘贴逻辑，先复制全量单元格格式，再直接读取公式文本批量替换源工作簿标记即可，执行效率远高于逐单元格处理：

Sub CopyWithoutExternalLink()
    Dim sourceSht As Worksheet, targetSht As Worksheet
    Dim linkTag As String
    
    ' 绑定源工作表与目标工作表
    Set sourceSht = Workbooks("old.xlsm").Worksheets("Bookings")
    Set targetSht = ThisWorkbook.Worksheets("Bookings")
    
    ' 先复制所有单元格格式、条件格式、数据验证等非公式内容
    sourceSht.Cells.Copy
    targetSht.Range("A1").PasteSpecial Paste:=xlPasteAllMergingConditionalFormats
    Application.CutCopyMode = False
    
    ' 构造公式中需要移除的源文件标记
    linkTag = "[" & sourceSht.Parent.Name & "]"
    
    ' 批量替换公式中的外部链接标记
    With targetSht.UsedRange
        If .HasFormula Then
            .Formula = Application.Substitute(.Formula, linkTag, "")
        End If
    End With
End Sub

注意：如果源表公式存在跨工作表引用（比如示例中引用同个old.xlsm内的Flexi Planner工作表），替换后公式会自动匹配当前工作簿内的同名工作表，只要提前将源工作簿中所有被引用的工作表按同名复制到当前工作簿，公式即可正常计算，完全保留原有逻辑。

方案2：手动查找替换（无需写代码） #

如果不需要自动化处理，可按以下手动步骤操作：

1. 照常使用PasteSpecial粘贴公式和格式到目标工作簿
2. 选中目标工作表所有已用单元格，按Ctrl+H打开查找替换窗口
3. 查找内容输入[old.xlsm]，替换为内容留空，点击「全部替换」即可
   该方法逻辑和VBA方案完全一致，不会破坏公式结构。

不要使用xlPasteValues粘贴模式或者内置的「断开链接」功能，这两种操作都会直接将公式转为静态值，丢失原有计算逻辑。

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