将工作表复制到带模块的新工作簿：VBA模块复制失败及报错咨询

一、解决“Programmatic access to Visual Basic Project is not trusted”错误 #

这个1004错误是Excel默认安全限制导致的，需要调整信任中心设置：

• 打开Excel，依次点击「文件」→「选项」→「信任中心」→「信任中心设置」
• 切换到「宏设置」选项卡，勾选信任对VBA项目对象模型的访问
• 确认设置后重启Excel，再运行代码即可解除权限限制

二、修正代码逻辑，确保模块成功复制 #

之前的代码存在对象引用混乱的问题，比如误将新工作簿当作模块导出源，或保存操作对象错误。以下是修正后的完整可用代码：

Sub CopySheetsAndModule()
    Dim path As String
    Dim filename1 As String
    Dim sourceWb As Workbook
    Dim newWb As Workbook
    Dim strTempFile As String
    
    ' 绑定原工作簿对象，避免ActiveWorkbook切换导致的错误
    Set sourceWb = ThisWorkbook
    
    ' 获取目标文件名和保存路径
    filename1 = sourceWb.Worksheets("Instructions").Range("P1").Text & " Tech Eval"
    path = sourceWb.path & "\"
    
    ' 复制指定工作表到新工作簿，此时新工作簿会成为活动工作簿
    sourceWb.Worksheets(Array("Narrative", "Summary Page", "Exceptions", "Offer Rating Sheet")).Copy
    Set newWb = ActiveWorkbook
    
    ' 将新工作簿保存为启用宏的格式（必须，否则无法导入模块）
    newWb.SaveAs Filename:=path & filename1, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
    ' 导出原工作簿中的Module2到临时文件
    strTempFile = path & "~tmpexport.bas"
    sourceWb.VBProject.VBComponents("Module2").Export strTempFile
    
    ' 将临时文件中的模块导入新工作簿
    newWb.VBProject.VBComponents.Import strTempFile
    
    ' 清理临时文件
    Kill strTempFile
    
    ' 保存新工作簿的模块导入修改
    newWb.Save
End Sub

关键修正点： #

• 明确区分sourceWb（原工作簿）和newWb（新工作簿），彻底避免ActiveWorkbook切换带来的对象指向错误
• 先创建新工作簿再保存为启用宏格式，保证新工作簿支持VBA模块
• 模块导出操作明确指向原工作簿，导入操作指向新工作簿，对象引用准确
• 移除了隐藏错误的On Error Resume Next，便于排查后续问题

内容的提问来源于stack exchange，提问作者Ryan Data Guy