插入带VBA的Excel模板工作表时宏未复制的解决问询

当通过「右键现有工作表→插入→选择模板」添加工作表时，Excel仅复制工作表的单元格内容、格式等元素，不会同步复制模板工作簿中的VBA标准模块、类模块或ThisWorkbook事件代码，这就是宏无法正常运行的原因。而通过「新建→模板」创建工作簿时，整个模板的所有组件（包括VBA代码）都会被导入到新工作簿，因此宏可以正常工作。

以下是三种可行的解决方案：

方案1：预先导入模板的VBA代码到目标工作簿 #

如果需要在现有工作簿中多次插入该模板工作表，可先将模板中的宏代码导入目标工作簿：

• 打开模板工作簿和目标工作簿，按Alt+F11切换到VBA编辑器。
• 在模板工作簿的「工程」窗口中，右键需要复制的标准模块→选择「导出文件」，保存为.bas格式文件。
• 在目标工作簿的「工程」窗口中，右键空白处→选择「导入文件」，选择刚才导出的.bas文件。
• 若模板的ThisWorkbook中有事件代码（如你的Workbook_SheetChange），需将代码复制到目标工作簿的ThisWorkbook代码页中。

完成后，目标工作簿已具备所有所需宏代码，后续插入模板工作表时，Workbook_SheetChange事件可正常调用对应的Sub过程。

方案2：用VBA代码实现「插入工作表+同步复制宏」 #

编写一个自定义宏，自动完成插入模板工作表并同步复制VBA代码的操作，避免手动导入的繁琐：

Sub InsertTemplateSheetWithMacros()
    Dim templatePath As String
    Dim templateWB As Workbook
    Dim targetWB As Workbook
    Dim newWS As Worksheet
    Dim templateModule As Object
    
    ' 替换为你的模板文件路径
    templatePath = "C:\Templates\YourStrataTemplate.xltm"
    Set targetWB = ThisWorkbook
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    ' 打开只读模板
    Set templateWB = Workbooks.Open(templatePath, ReadOnly:=True)
    
    ' 复制模板工作表到目标工作簿末尾
    templateWB.Sheets(1).Copy After:=targetWB.Sheets(targetWB.Sheets.Count)
    Set newWS = targetWB.Sheets(targetWB.Sheets.Count)
    newWS.Name = "新分层计划表" ' 自定义工作表名称
    
    ' 复制模板中的标准模块到目标工作簿（跳过已存在的模块）
    For Each templateModule In templateWB.VBProject.VBComponents
        If templateModule.Type = vbext_ct_StdModule Then
            On Error Resume Next
            Dim existingModule As Object
            Set existingModule = targetWB.VBProject.VBComponents(templateModule.Name)
            On Error GoTo 0
            
            If existingModule Is Nothing Then
                ' 若目标工作簿无同名模块，导入模板模块
                targetWB.VBProject.VBComponents.Import templateModule.CodeModule.FileName
            End If
        End If
    Next
    
    ' 同步ThisWorkbook事件代码（仅当目标工作簿无该代码时）
    Dim targetThisCode As CodeModule
    Set targetThisCode = targetWB.VBProject.VBComponents("ThisWorkbook").CodeModule
    If targetThisCode.CountOfLines = 0 Then
        targetThisCode.AddFromString _
            templateWB.VBProject.VBComponents("ThisWorkbook").CodeModule.Lines(1, _
            templateWB.VBProject.VBComponents("ThisWorkbook").CodeModule.CountOfLines)
    End If
    
    ' 关闭模板工作簿
    templateWB.Close SaveChanges:=False

CleanExit:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

注意：需在Excel信任中心开启「信任对VBA项目对象模型的访问」，否则代码无法操作VB组件。路径：「文件→选项→信任中心→信任中心设置→宏设置」。

方案3：将宏代码封装到工作表类模块 #

把所有宏代码移到模板工作表的代码页中，这样复制工作表时会同步复制代码：

1. 打开模板工作簿的VBA编辑器，双击模板工作表（如Sheet1）进入其代码页。
2. 将原ThisWorkbook中的Workbook_SheetChange代码修改为工作表级的Worksheet_Change事件，把ws替换为Me（代表当前工作表）：

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    On Error GoTo CleanExit

    If Me.Range("A1").Value = "Strata Plan #:" Then
        If Not Intersect(Target, Me.Range("E26:E32")) Is Nothing Then
            Call InvestmentAccounts(Me)
        End If
        ' 保留原有的其他判断逻辑，统一替换ws为Me
        ' ...省略其余判断代码...
    End If

CleanExit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Me.Calculate
    Me.Activate
End Sub

3. 将标准模块中的所有Sub过程（如InvestmentAccounts、S110Notes等）复制到该工作表代码页中，修改为Private或Public（若需其他工作表调用）。

这样，每次复制该模板工作表时，工作表的代码页会被一起复制，新工作表自带完整的事件和宏代码，无需依赖工作簿级模块。

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