Please help macro for save

[Guest]

I think a macro is the best way to do this; if there is a way that is better
I am open to all suggestions. I am trying to create a backup copy of one of
my spreadsheets on a daily basis. I need this to be done every weekday night
@ around 11PM when I know all updates for that day should be done. I need the
file name to contain the date from that day. So far I have the macro below as
my starting point. I have tried doing different things to get what I want; I
have had varying degrees of success. If anyone has any ideas as to how I
should attack this I would be very grateful.
Thanks in advance.




Sub Saveas()
'
' Saveas Macro
' Macro recorded 10/11/2007 by
'

'
ChDir _
"R:\Production Reports\Production Summary Report\Daily Production
Summary Copies"
ActiveWorkbook.Saveas Filename:= _
"R:\Production Reports\Production Summary Report\Daily Production
Summary Copies\Book1.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

 

[Pete_UK]

When you record a macro you often get things you don't really need.
Here you change the directory, so you don't need the full path in the
SaveAs command.

Anyway, do you really want to save it as Book1 (with a date added)? If
so, then you can try this:

ActiveWorkbook.Saveas Filename:= _
"Book1" & Format(Date, "yymmdd") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Note the changes following "Book 1" on the second line. You might want
to change the format string.

Hope this helps.

Pete