VBA - Save document without duplicating VBA code

Question

I have a spreadsheet workbook that I'm using as a template.

The macro uses "SaveAs" to make a fresh copy of the workbook; this includes the macros themselves. At that point the template version is closed, and I'm working with a fresh version of the macro too.

I don't mind having duplicates of the macros, but any changes I make should be in the template version.

I tried saving the workbook as a non-macro-enabled version, thinking that there would only be the original macro module, but the new version still has the macros! They just "go away" when the workbook is saved. (Which means I could lose any changes I accidentally made there).

So I have these questions:

• Can I create a new workbook from the template without closing the template? At least that way, I can still have the original, as well as the new version, without having to reopen it.

• Can I force the processing to always use the macros in the template and not the new version? That way I'm more likely to have the template version in front of me, instead of the other.

• Is there some way I'm not thinking of to be sure I don't make the edits in the wrong place?

Answer 1

This isn't going to be one of those satisfying answers, but the right answer is that your code should be separate from your template. Your code should live in an add-in and your template should be an .xlsx file. That way you always have one version of the code.