Macro HELP PLEASE

D

DP7

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
 
O

OssieMac

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
 
B

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)
 
O

OssieMac

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
 
O

OssieMac

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MACRO SAVE HELP 2
Please help macro for save 1
Macro Time stamp Help 2
making a countdown timer 1
Stopping a timer 10
VBA coding 3
Timer event 2
AutoSave 2

Top