Arguments problem repost

[Seeker]

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

 

[geoff_ness]

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

 

[Seeker]

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

 

[Seeker]

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