Excel VBA如何插入单元格实际值而非公式，解决#REF引用报错

问题原因 #

原代码使用Copy+Insert组合操作时，默认会携带单元格公式跨工作簿粘贴，源工作簿引用断开后就会出现#REF错误。直接调用.Value属性不生效，是因为没有提前在目标表腾出对应尺寸的插入空间，也没有绕过剪贴板的默认带格式/公式复制逻辑。

修复后代码 #

Sub PullTally()
    Dim pullFile As Variant, putFile As Variant
    Dim pullRng As Range, putRng As Range
    Dim dataArr As Variant
    Dim rowNum As Long, colNum As Long
    
    '选择源、目标文件
    pullFile = Application.GetOpenFilename(fileFilter:="Excel Files (*.xlsx;*.xls;*.xlsm), *.xlsx;*.xls;*.xlsm")
    putFile = Application.GetOpenFilename(fileFilter:="Excel Files (*.xlsx;*.xls;*.xlsm), *.xlsx;*.xls;*.xlsm")
    '用户点取消时直接退出过程
    If pullFile = False Or putFile = False Then Exit Sub

    '读取源文件指定区域的纯值（不含公式）
    Set pullRng = Workbooks.Open(Filename:=pullFile).Worksheets("Sheet1").Range("K4:M19")
    rowNum = pullRng.Rows.Count
    colNum = pullRng.Columns.Count
    dataArr = pullRng.Value '直接把单元格显示内容读入数组，不携带公式引用
    pullRng.Parent.Parent.Close SaveChanges:=False '读完直接关源文件，不占内存

    '打开目标文件，在指定位置腾出对应大小的下移空间
    Set putRng = Workbooks.Open(Filename:=putFile).ActiveSheet.Range("A100")
    putRng.Resize(rowNum, colNum).Insert xlShiftDown

    '把纯值写入腾出的插入位置
    putRng.Resize(rowNum, colNum).Value = dataArr

    MsgBox "数据插入完成"
End Sub

核心改动说明 #

• 完全绕过系统剪贴板的Copy逻辑，直接读取源区域的显示值存入数组，从根源上避免公式引用带来的#REF问题
• 插入操作前先根据源区域的行列数，在目标位置腾出同等尺寸的下移空间，和原需求的插入下移效果完全一致
• 增加文件选择取消的容错判断，避免用户点取消时触发运行错误
• 读取完源文件数据后直接关闭源工作簿，不会残留多余打开窗口
• 调整文件筛选规则为Excel常用格式，避免误选非Excel文件导致报错

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