Excel VBA：复制含Indirect公式工作表至新工作簿并转值的高效方法

核心思路 #

• 不在新工作簿中处理公式转值，而是在源工作簿内先生成目标工作表的值化临时副本，避免修改原工作表的同时脱离对DATA表的依赖
• 复制这些值化后的临时表到新工作簿，最后清理源工作簿里的临时文件，全程无需关注自动生成的工作表命名

修改后的VBA代码 #

Dim sWorkSheetNames() As Variant
sWorkSheetNames = Array("Daily Summary", "Monthly Summary")

' 引用源工作簿
Dim swb As Workbook: Set swb = ThisWorkbook
Dim tempSheets As New Collection ' 存储临时工作表，用于后续批量操作
Dim wsName As Variant
Dim srcWs As Worksheet, tempWs As Worksheet

' 遍历目标工作表，生成值化的临时副本
For Each wsName In sWorkSheetNames
    Set srcWs = swb.Worksheets(wsName)
    ' 复制原工作表到源工作簿末尾，创建临时副本
    srcWs.Copy After:=swb.Worksheets(swb.Worksheets.Count)
    Set tempWs = swb.Worksheets(swb.Worksheets.Count)
    tempWs.Name = "Temp_" & wsName ' 给临时表命名，避免名称冲突
    ' 将临时表的已用区域公式转为值
    tempWs.UsedRange.Value = tempWs.UsedRange.Value
    tempSheets.Add tempWs ' 把临时表加入集合记录
Next wsName

' 复制所有临时工作表到新工作簿
Dim tempSheetNames() As String
ReDim tempSheetNames(1 To tempSheets.Count)
Dim i As Integer
For i = 1 To tempSheets.Count
    tempSheetNames(i) = tempSheets(i).Name
Next i
swb.Worksheets(tempSheetNames).Copy

' 引用新生成的工作簿
Dim dwb As Workbook: Set dwb = Workbooks(Workbooks.Count)

' 删除源工作簿中的临时工作表
Application.DisplayAlerts = False ' 关闭删除确认提示
For Each tempWs In tempSheets
    tempWs.Delete
Next tempWs
Application.DisplayAlerts = True

' 可选：移除新工作簿工作表的临时前缀，恢复原名称
Dim destWs As Worksheet
For Each destWs In dwb.Worksheets
    destWs.Name = Replace(destWs.Name, "Temp_", "")
Next destWs

代码说明 #

• 临时副本机制：在源工作簿内复制目标表生成临时文件，转值操作直接在源环境完成，彻底避免复制后公式依赖缺失的问题
• 集合管理临时表：用Collection统一管理临时表对象，方便后续批量复制和删除，无需手动追踪临时表名称
• 静默清理：删除临时表时关闭DisplayAlerts，避免弹出确认窗口，提升操作流畅度
• 可选重命名：如果不需要临时前缀，可通过替换操作恢复工作表原名称，保持和源表一致

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