date in filename

[Guest]

I would like to write a macro that will save a file with the date in the file
name before it prints, print the sheet, clear the data, then save as a
different file. Here's what I have:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:="J:\My Documents\APS Bodies &
Options\Pacific-APS Order List " & Left(Now(), 8) & ".xls"
Application.EnableEvents = False
Cancel = True
ActiveSheet.PrintOut
Application.EnableEvents = True
Range("A16:J41").ClearContents
ActiveWorkbook.SaveAs Filename:="J:\My Documents\APS Bodies &
Options\Pacific-APS Order List.xls"
End Sub

I would like the file that is saved to have this file name: Pacific-APS
Order List 040405.xls. I can't figure out how to get the now() function to do
this. Any suggestions? I need to be able to do this any month or any day of
the year.

 

[David McRitchie]

Suggest you use 20050404 for the date yyyymmdd so you can
look at the directory in alphabetical order.

http://www.mvps.org/dmcritchie/excel/backup.htm