Macro HELP PLEASE

[Guest]

I need to fix that macro so that I could auto
run it. Now here is my next problem. I need to auto run this macro. I have
the put the procedure in that is found below & been testing it. It seems not
to be working for me form some strange reason. If you have any suggestions as
to what I am doing wrong you r help would be welcome. If you are wondering my
ultimate goal is to have the spreadsheet save a copy of itself every night @
11:07PM with that days date. Thanks again

Public RunWhen As Double
Public Const cRunWhat = "Saveas" ' the name of the procedure to run
Sub StartTimer()
RunWhen = TimeSerial(14, 48, 27)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=True
End Sub
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\Production Summary Report777" & Format(Date, "mm-dd-yy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

 

[Guest]

First thing I see is:-
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,Schedule:=True

Procedure set to run 'cRunWhat' and the procedure you posted is 'Saveas'.

Regards,

OssieMac

 

[Bob Phillips]

Isn't 11:07PM equal to TimeSerial(23, 07, 00)?

And if you want it each day, you need to reschedule after re-running it.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Another problem is your time:-
RunWhen = TimeSerial(14, 48, 27)
That time is 2:48:27 PM

For 11:07 PM should be:-
RunWhen = TimeSerial(23, 7, 0)

Regards,

OssieMac

 

[Guest]

My apologies. My first post was incorrect. I didn't read the code properly
and missed the constant with the procedure name. My second post must have
been going in while Bob was posting his. However, I tested the code and it
will work but see Bob's comment about rescheduling.

As a suggestion: Once you have changed directory, it is not necessary to
include the path in the save as file name. Could use the following:-

ActiveWorkbook.Saveas Filename:= _
"Production Summary Report777" & Format(Date, "mm-dd-yy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Regards,

OssieMac