Excel template's VBA code not working after I insert it into a new workbook

Question

I create an Excel template with fixed format and VBA stored in.

Whenever I insert the Excel template into a new workbook, the VBA code will not follow and apply. Only the format follows.

Does anyone know why?

This is the code stored in Template.xltm. Under ThisWorkbook

Sub Auto_Open()

Workbooks.Open Filename:="C:\Users\ASUS User\Desktop\MP\source.xlsm"
ThisWorkbook.Activate


End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Columns(2).AutoFit
Rows().AutoFit
End Sub



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wBook As Workbook
On Error Resume Next
Set wBook = Workbooks("source.xlsm")

If wBook Is Nothing Then
'The Workbook Is Not open'
' Nothing to do as workbook is not open
Set wBook = Nothing
On Error GoTo 0
Else 'Workbook is open
' Close the workbook and save any changes
Workbooks("source.xlsm").Close SaveChanges:=True
Set wBook = Nothing
On Error GoTo 0
End If
End Sub

Does VBA code in excel macro enable template follows the template?

What is the extension of your file ? Did you save it as xltm — Imran Malek
Where is your VBA code saved in the .xltm workbook? Please read How to Ask and edit your question to include more detail about your problem. As it currently stands it is very difficult to know why your template is not working as desired. Try and create a minimal reproducible example so we can test it on our own machines and give a sample of your VBA code. — girlvsdata

girlvsdata girlvsdata · Accepted Answer · 2018-08-13T06:41:01

You need to make sure your workbook is saved as a Macro-enabled template, with the file extension .xltm instead of the standard .xltx template extension.

Excel template's VBA code not working after I insert it into a new workbook

1 Answers