I posted following question on yesterday, oddly enough that I have been
looking for more then 2 hours and I still cant find the thread on today.
I intended to save a workbook by using SaveAs in Workbook_BeforeClose in 3
different file names and paths just in case users forget to save file before
they close the book.
XXX.xls - 1st file overwrite current file name, data carry forward
XXXbackup.xls €“ 2nd file same as 1st file, just in case of the 1st one gets
corrupted
DateXXX.xls €“ 3rd file name add date, as record keeping on that date.
Since the 3rd file is a history file, if opening for review is needed on
following day and onward, how to avoid it (set the logic) overwrite the
current 1st & 2nd files?
Similarly, a macro auto adding =Today() in cell A1 of sht 1 for calculation
on Workbook_Open, how to prevent (set the logic) the date change to =Today()
in the 3rd file when it being opened from following day and onward?
Regards

in the sbefore-close, check what the filename is before saving

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Name = "xxx.xls" Then

Me.SaveCopyAs "xxxBackup.xls"
Me.SaveCopyAs format$(Date,"YYYYMMDD") & "xxxBackup.xls"

End Sub


"Seeker" wrote:

I posted following question on yesterday, oddly enough that I have been
looking for more then 2 hours and I still cant find the thread on today.
I intended to save a workbook by using SaveAs in Workbook_BeforeClose in 3
different file names and paths just in case users forget to save file before
they close the book.
XXX.xls - 1st file overwrite current file name, data carry forward
XXXbackup.xls €“ 2nd file same as 1st file, just in case of the 1st one gets
corrupted
DateXXX.xls €“ 3rd file name add date, as record keeping on that date.
Since the 3rd file is a history file, if opening for review is needed on
following day and onward, how to avoid it (set the logic) overwrite the
current 1st & 2nd files?
Similarly, a macro auto adding =Today() in cell A1 of sht 1 for calculation
on Workbook_Open, how to prevent (set the logic) the date change to =Today()
in the 3rd file when it being opened from following day and onward?
Regards

Hi Patrick,
Thanks for your code, I looked for it for two days already, thans again.
Rgds

"Patrick Molloy" wrote:

in the sbefore-close, check what the filename is before saving

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Name = "xxx.xls" Then

Me.SaveCopyAs "xxxBackup.xls"
Me.SaveCopyAs format$(Date,"YYYYMMDD") & "xxxBackup.xls"

End Sub


"Seeker" wrote:

I posted following question on yesterday, oddly enough that I have been
looking for more then 2 hours and I still cant find the thread on today.
I intended to save a workbook by using SaveAs in Workbook_BeforeClose in 3
different file names and paths just in case users forget to save file before
they close the book.
XXX.xls - 1st file overwrite current file name, data carry forward
XXXbackup.xls €“ 2nd file same as 1st file, just in case of the 1st one gets
corrupted
DateXXX.xls €“ 3rd file name add date, as record keeping on that date.
Since the 3rd file is a history file, if opening for review is needed on
following day and onward, how to avoid it (set the logic) overwrite the
current 1st & 2nd files?
Similarly, a macro auto adding =Today() in cell A1 of sht 1 for calculation
on Workbook_Open, how to prevent (set the logic) the date change to =Today()
in the 3rd file when it being opened from following day and onward?
Regards

i did miss off the END IF statement - it obviously wnet in just befor the
end sub
..
"Seeker" wrote:

Hi Patrick,
Thanks for your code, I looked for it for two days already, thans again.
Rgds

"Patrick Molloy" wrote:

in the sbefore-close, check what the filename is before saving

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Name = "xxx.xls" Then

Me.SaveCopyAs "xxxBackup.xls"
Me.SaveCopyAs format$(Date,"YYYYMMDD") & "xxxBackup.xls"

End Sub


"Seeker" wrote:

I posted following question on yesterday, oddly enough that I have been
looking for more then 2 hours and I still cant find the thread on today.
I intended to save a workbook by using SaveAs in Workbook_BeforeClose in 3
different file names and paths just in case users forget to save file before
they close the book.
XXX.xls - 1st file overwrite current file name, data carry forward
XXXbackup.xls €“ 2nd file same as 1st file, just in case of the 1st one gets
corrupted
DateXXX.xls €“ 3rd file name add date, as record keeping on that date.
Since the 3rd file is a history file, if opening for review is needed on
following day and onward, how to avoid it (set the logic) overwrite the
current 1st & 2nd files?
Similarly, a macro auto adding =Today() in cell A1 of sht 1 for calculation
on Workbook_Open, how to prevent (set the logic) the date change to =Today()
in the 3rd file when it being opened from following day and onward?
Regards