I posted following question on yesterday, oddly enough that I have been
looking for more then 2 hours and I still can’t 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 can’t 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 can’t 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 can’t 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
> > >