使用VBA复制工作表到新工作簿出现#REF错误如何解决

问题根源 #

你原有代码出现#REF错误、无法保留图表和分组的核心原因有3个：

• 先把工作表复制到新工作簿再做值粘贴，此时新工作簿没有公式依赖的其他源工作表，公式在粘贴前已经计算报错生成#REF
• 设置了Application.CopyObjectsWithCells = False，导致图表无法跟随工作表复制
• 仅对第一个SSH工作表做了值粘贴处理，第二个MIL工作表未处理，且全选复制粘贴的方式容易破坏分组结构

修复后可用代码 #

Sub ExportSSHMIL()
    Dim srcWB As Workbook
    Dim newWB As Workbook
    Dim ws As Worksheet
    Dim arrSheetNames As Variant
    
    ' 配置参数
    arrSheetNames = Array("SSH New Starts by Site by Week", "MIL New Starts by Site by Week")
    Set srcWB = ThisWorkbook
    
    ' 关闭屏幕更新提升速度，开启对象复制保留图表
    Application.ScreenUpdating = False
    Application.CopyObjectsWithCells = True
    
    ' 批量复制指定工作表到新工作簿
    srcWB.Sheets(arrSheetNames).Copy
    Set newWB = ActiveWorkbook
    
    ' 遍历新工作簿内所有目标工作表，批量转值保留格式和分组
    For Each ws In newWB.Worksheets
        With ws.UsedRange
            ' 直接赋值替换公式为值，不改动格式、分组、图表
            .Value = .Value
        End With
        ' 重置选中状态避免全选高亮
        ws.Range("A1").Select
    Next ws
    
    ' 恢复系统设置
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    
    ' 可选：自动保存新工作簿到指定路径，按需取消注释修改路径即可
    ' newWB.SaveAs Filename:="C:\你的保存路径\周报表_" & Format(Date, "yyyy-mm-dd") & ".xlsx", FileFormat:=xlOpenXMLWorkbook
End Sub

代码说明 #

• 调整操作逻辑：直接通过Value属性赋值替换公式，比复制粘贴更高效，且不会破坏原有列分组、单元格格式、图表等元素
• 开启CopyObjectsWithCells确保图表可以跟随工作表完整复制到新工作簿
• 增加遍历逻辑，自动对所有导出的工作表做转值处理，不需要逐个手动指定工作表名

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