VBA执行SaveAs另存为xlsm文件时宏模块丢失如何解决

问题根因 #

你当前使用的Worksheets(tabs).Copy逻辑仅会复制选中的工作表对象，仅能迁移工作表内绑定的事件代码，不会携带原工作簿的标准模块、类模块、用户窗体、ThisWorkbook全局事件代码等VBA组件，即便另存为xlsm格式也不会包含宏内容。

推荐解决方案：整簿复制后删除多余工作表（兼容性最好，无需额外配置） #

这个方案直接复用原工作簿的所有宏配置，不需要操作VBA工程对象，代码修改如下：

Sub PushToProduction()
    Application.ScreenUpdating = False
    Dim originalWb As Workbook
    Dim newWb As Workbook
    Set originalWb = ThisWorkbook
    
    ' 拼接输出路径
    outputPath = Environ$("USERPROFILE") & "\Downloads\"
    d = Format(Date, "yyyymmdd")
    fileName = outputPath & "REDACTED " & d & " v1.00.xlsm"
    
    ' 先删除已存在的同名文件避免保存报错
    On Error Resume Next
    Kill fileName
    On Error GoTo 0
    
    ' 保存原工作簿的完整副本到目标路径
    originalWb.SaveCopyAs Filename:=fileName
    
    ' 打开新保存的文件，删除最后一个工作表
    Set newWb = Workbooks.Open(fileName)
    Application.DisplayAlerts = False
    newWb.Sheets(newWb.Sheets.Count).Delete ' 删除最后一个标签
    Application.DisplayAlerts = True
    
    ' 保存并关闭新文件
    newWb.Save
    newWb.Close SaveChanges:=False
    
    Application.ScreenUpdating = True
    MsgBox "Success!"
End Sub

可选方案：复制工作表后批量迁移VBA组件 #

如果业务场景要求必须先复制指定工作表再生成新文件，可以调用VBA的VBIDE对象实现代码迁移，使用前需要先进入Excel信任中心→宏设置→勾选「信任对VBA工程对象模型的访问」，代码示例如下：

' 需先添加引用：工具→引用→勾选「Microsoft Visual Basic for Applications Extensibility 5.3」
Sub CopyVBAComponents(sourceWb As Workbook, targetWb As Workbook)
    Dim comp As VBComponent
    For Each comp In sourceWb.VBProject.VBComponents
        ' 排除原有工作表、ThisWorkbook等内置对象，仅复制模块、窗体、类模块
        If comp.Type <> vbext_ct_Document Then
            comp.Export Environ$("TEMP") & "\" & comp.Name
            targetWb.VBProject.VBComponents.Import Environ$("TEMP") & "\" & comp.Name
            Kill Environ$("TEMP") & "\" & comp.Name
        End If
    Next
End Sub

在你的原有代码Worksheets(tabs).Copy之后调用该方法即可：Call CopyVBAComponents(ThisWorkbook, ActiveWorkbook)

注意事项 #

• 优先使用第一种方案，无需修改Excel安全配置，适配所有场景
• 如果存在隐藏的工作表需要保留，调整删除工作表的逻辑，避免误删
• 路径拼接时建议判断Downloads文件夹是否存在，避免特殊环境下路径不存在报错

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