I have a macro that worked, until I attempted to add an upgrade to it. I mean the old portion work (saving of file), but the new task is to save it in my backup folder. It will save, but with the "template name" rather than the audit file name.
The macro adds a check box once run (there are multiple macros in this workbook. The Insert_End marco that is call just adds the date/time that the process is complete. Now it is copying the template name that it currently exists as (such as Word Comment Extraction Tool v165.xlsb). I would like it to save the backup file with the IDENTICAL file name as for the Audit File (this file name is listed on "Main" sheet cell B21 (without an extension), this is
Sub SaveAudit()
' Save file as multiple cell references
' Macro Complete - Checkmark added (to Main Sheet)
Sheet7.Range("$K$8").Value = ChrW(&H2713)
Call Insert_End
Dim fname As String
Dim FPath As String
Dim backupfolder As String
'Save Active Workbook
ThisWorkbook.Save
'Save COPY of Active Workbook (with new filename)
'Path of file
FPath = Sheets("Main").Range("B7").Value
'New file name WITHOUT extension
fname = Sheets("Main").Range("B21").Text & ".xlsb"
ThisWorkbook.SaveCopyAs Filename:=FPath & "" & fname
' Save Backup file
backupfolder = Sheets("codes").Range("O2").Text & ".xlsb"
ThisWorkbook.SaveCopyAs Filename:=backupfolder & ThisWorkbook.Name
'Save Confirmation
If Err.Number <> 0 Then
MsgBox Err.Description
Else
MsgBox "Original File saved AND a Copy of the File Saved as " & FPath & "" & fname
End If
Call Save_BackUp
'' ' Save Workbook & Close File (NEW Workbook) ' Deactivated until Template has been updated!
'' ActiveWorkbook.Close SaveChanges:=True
ActiveWorkbook.Save
End Sub
Since you're using ThisWorkbook.Name it's the name of the currenbtly active file whihc is the saved file
Instead just refer to the B2 cell text to save it as the 'original name but you have to add a dispaly alerts is false if you want to overwrite it automatically
@Hans Hallebeek,
I made a change, and now what happens it BOTH my Audit file with the correct name generated from the cell values AND the template name show in my backup folder. I just don't understand how to fix it, but I did make progress.
Sub SaveAudit()
' Save file as multiple cell references
' Macro Complete - Checkmark added (to Main Sheet)
Sheet7.Range("$K$8").Value = ChrW(&H2713)
Call Insert_End
'' Call Formula_Value
Dim fname As String
Dim FPath As String
Dim backupfolder As String
' Save Active Workbook
ThisWorkbook.Save
' Save Audit file
' Save COPY of Active Workbook (with new filename)
'Path of file
FPath = Sheets("Main").Range("B7").Value
' New file name WITHOUT extension
fname = Sheets("Main").Range("B21").Text & ".xlsb"
ThisWorkbook.SaveCopyAs Filename:=FPath & "" & fname
' Save Backup file
' Save COPY of Active Workbook (with new filename)
'Path of file
FPath = Sheets("codes").Range("O2").Value
' New file name WITHOUT extension
fname = Sheets("Main").Range("B21").Text & ".xlsb"
ThisWorkbook.SaveCopyAs Filename:=FPath & "" & fname
'Save Confirmation
If Err.Number <> 0 Then
MsgBox Err.Description
Else
MsgBox "Original File saved AND a Copy of the File Saved as " & FPath & "" & fname
End If
Call Save_BackUp
'' ' Save Workbook & Close File (NEW Workbook) ' Deactivated until Template has been updated!
'' ActiveWorkbook.Close SaveChanges:=True
ActiveWorkbook.Save
End Sub
Make sure the filepaths are correct for each file.
Can you attach the template file with the values in the different sheets?
Just some dummy data is enough but it will be simpler that way.
Try using this section of the code like this, check the red text
' Save Backup file
' Save COPY of Active Workbook (with new filename)
'Path of file
backupfolder = Sheets("codes").Range("O2").Value
' New file name WITHOUT extension
fname = Sheets("Main").Range("B21").Text & ".xlsb"
ThisWorkbook.SaveCopyAs Filename:=backupfolder & "" & fname
'Save Confirmation
If Err.Number <> 0 Then
MsgBox Err.Description
Else
MsgBox "Original File saved AND a Copy of the File Saved as " & backupfolder & "" & fname
End If
@Hans Hallebeek,
I made the modifications per your suggestion. However, I am still encountering one challenge. The file is saving in BOTH the individual project folder, and the back-up folder. However the issue is that the Template (Word Comment Extraction Tool v170.xlsb) is ALSO saving in the back-up folder. This folder should NOT be saved in the back-up folder. How can I fix this?
I'll have to read the code you have more thouroughly.
The problem lies in the file names used
@Hans Hallebeek,
Thanks again. I just have no clue why it is saving BOTH of these files. I am sure it must be something simple, but I just cannot see it.
Very useful thank you
@Sherry Fow, I'm missing two macros,
Save_BackUp
Insert_End
Since you also addres diffrente Sheets like Sheet7, Main,
I suggest you attach a copy of your template file and maybe that way I can make sense of all what you're trying
