I have a workbook with a template file in a secure location (“M:template.xlsm”). I have an identical file in an editable location (“s:working template.xlsm”. After someone is done with the working template, the code saves it to a third “archive” location with a variable name also in the S drive.
I am running a software that can only send data to “working template.xlsm”. I want to be able to copy the template and replace the working template with the template and keep the working template name.

(for context, I was just erasing the data that was added after I made the archive file. But some of the formulas are getting messed up during data entry, so then they are messed up forever. It only erases the data, it does not replace anything)

The problem is that excel wont overwrite and open file. I was going to copy the file and paste it in the working template, but I think I need all of the code to transfer. There is a lot of modules linked to sheet 1 and 2 and will those transfer if I just copy sheet 1 and 2 from the template?
A more complicated idea that might just cause more problems:
open all three (template, working template, archive file)
close the working template file
from the archive file, save the template as working template.

Something I am worried about is I think you would have to do it when the workbook opens since you are closing the file that the code is running on (working template). So then every time you open the archive it would do this. Also the archive is a save as copy of the working template sheet 1. So it does not take the code with it. If it did take the code with it, then when you open the working template it would just keep copying from template and i would lose the archived data.

I am not a practiced programmer, so I am sure there are ideas that I am missing. Any help or ideas would be appreciated!