excel vba code is gone after saving copy

Question

I have a template in which our workers can fill in the data, then the file should be saved as a new file with the Original macros from my template in it but that doesn't seem to work. The code is always removed in the new file. The template should be saved too because there's a numbering in it that has to go up with every new file created.

Does anybody know the solution?

Public Sub OpslBestand()
On Error Resume Next
Book1_BeforeSave
SavePdfEmail
Dim NieuwRet As Variant
ActiveWorkbook.Sheets.Copy  
NieuwRet = "P:\Retourformulieren\Excel\" & Range("L2").Value & "-JL-" & Format(Date, "YYYYMMDD") & "-" & Range("Klant").Value
ActiveWorkbook.SaveAs NieuwRet, FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close
VolgRet
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

Guys i found the problem... It's this line ActiveWorkbook.Sheets.Copy this should be Workbook.Sheets.copy and then the macro is in the new file — Christophe
– Christophe, Commented Sep 8, 2017 at 9:45
When you do a Sheets.Copy it copies the sheets (and any macros in those sheets) to a new workbook, but won't copy any non-worksheet macros. — YowE3K
– YowE3K, Commented Sep 8, 2017 at 9:48
Yes that's the one thanks for your answer too, it was a luck for me that i tried this — Christophe
– Christophe, Commented Sep 8, 2017 at 9:48

Kevin · Accepted Answer · 2017-09-08 09:32:04Z

2

If you save the file as .xlsx, the macro will be deleted. You need to save the file as .xlsm (macro-enabled).

answered Sep 8, 2017 at 9:32

Kevin

1032 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

YowE3K Over a year ago

ActiveWorkbook.SaveAs NieuwRet, FileFormat:=xlOpenXMLWorkbookMacroEnabled will save as a macro-enabled file.

Collectives™ on Stack Overflow

excel vba code is gone after saving copy

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related