Log In
Register
Home
December 4, 2021
Offline
This macro works, but not quite the way I want it to. The macro is in my template file, which I work from. When I access this macro, my intention is to save a COPY of the template based on the variables that the cells are accessing. An example of the filename that would be generated from these cells is "D00920835-A_EN - 03-28-2023 - Audit File". Now my desire is for that to be a COPY of my template, showing all the inputs, and it would save and close, but still leaving the template file open for my next input. The COPYH saves and Closes. But I cannot figure out how to have the Template remain open. Thanks in advance!
Sub SaveAudit()
' Save file as multiple cell references
ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & Range("B15").Value & ".xlsb"
' Sheet7.Range("$B$2").Value & " - " & Format(Sheet7.Range("$B$14"), "mm-dd-yyyy") & " - Final File"
' Macro Complete - Checkmark added (to Main Sheet)
Sheet7.Range("$K$8").Value = ChrW(&H2713)
' Save Workbook & Close File (NEW Workbook) ' Deactivated until Template has been updated!
ActiveWorkbook.Close SaveChanges:=True
End Sub
Trusted Members
Moderators
November 1, 2018
Offline
What format is your template? Is it an actual template or a normal workbook that you use as a template and, if the latter, is it also saved in xlsb format like the copies you want to save?
December 4, 2021
Offline
@Velouria,
Sorry for not including that information. My "template" is actually just an .xlsb file rather than a traditional template (.xltm), as there are constant updates that are made to my file. I keep a Change Log and my file is versioned (even have e a "version macro for saving the update). So BOTH files are .xlsb format, what is the "active" file I am working with as well as the "template" version. And the Template version of my file is the one that needs to stay open.
Trusted Members
Moderators
November 1, 2018
Offline
In that case you can make whatever changes are required, then use
ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & Range("B15").Value & ".xlsb"
which will create a closed copy of the workbook. You will then either need code to undo whatever changes have been made from the original template state, or just close and reopen the original workbook without saving.
I don't follow your comment about the reasons for it not being an actual template. You can alter and version a template file in the same way you do any other.
December 4, 2021
Offline
@Velouria,
That did not quite work. Initially I received this error. Then I went to debug. I decided to take a copy of the file and place in downloads, and the length of the path is sometimes an error. In downloads it saved the "audit file", but the "template" did not remain open.
Trusted Members
Moderators
November 1, 2018
Offline
You haven't posted the error, or the full current code. SaveCopyAs will not close the current workbook but it will leave it in the same state it was in when the code ran, not in its initial 'template' state.
1 Guest(s)
