Geoff,
Please ignor my earlier reply as just read another thread that Patrick Molloy
has suggested following code, it avoids I run into my further messy coding.
However, your Date suggestion also solved my second worry.
Thanks again
Rgds
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:
> Hi Geoff,
> In fact, the date was added by VBA activecell.formula=date, thats mean I
> only need to add
> If ActiveWindow.Sheet("sheet").Range("A1")=ThisWorkbook.Name Then
> SaveAs...path .. file name
> Else
> End If
> Is above correct?
> Rgds
> "geoff_ness" wrote:
>
> > I would use the VBA Date function as opposed to using the Today
> > worksheet function. Today is volatile so it will always recalculate.
> > Alternatively, you can use the Today function, calculate and then
> > paste values - that way the date will persist. Then the problem of how
> > to avoid overwriting the current files can be resolved by checking
> > today's date against the date in your filename - run the save routine
> > only if the two are equal.
> >
> > HTH
> > Geoff
> >
> > On Oct 28, 7:16 pm, Seeker <See...@discussions.microsoft.com> 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
> >
> > .
> >
|
Similar Threads
