date in filename
 

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.

date in filename
 

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
--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"JCanyoneer" wrote in message ...
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.