Followup ? to message#17881

Original msg date is 5/18/01...Thanks in advance

Macro1 Macro 10am price cut&paste values

Macro1 Macro 10am price cut&paste values

Hi Darci, if I understand your problem correctly I think it relates to the line of code that reads:
Range("U4:U17").ClearContents

I think you need to change this to:
Range("U4:U17").Copy
Range("U4:U17").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False

Let me know if this doesn't work for you.

Regards,
Barrie

Barrie-it didn't work...

Macro1 Macro 10am price cut&paste values
It doesn't start on the time that i specify.. When i click run macro icon, it does the following
It returned the time, Macro1 ok ,and ended up in cell x8, and it copied and pasted fine.(again i omitted the lines about the refire blp, because it is not applicable on this computer) but the rest worked fine, except it doesn't start...
thank you very much for your help...

Re: Barrie-it didn't work...

Macro1 Macro 10am price cut&paste values

Hi Darci. When you say "it doesn't start", do you mean it doesn't run Macro1 at 10:00 am the next day (which is what the code appears to be doing)? I hate to ask an obvious question but....you're not shutting down the computer at the end of the day (or exiting Excel) are you?

Regards,
Barrie

Re: Barrie-it didn't work...

Macro1 Macro 10am price cut&paste values

I have been changing the time to test it each time, i will change the time to test like 2 minutes from now.. then saving the change and waiting for it to start and it does not do anything at the time specified.. does this help?
thanks again.
Darci

Re: Barrie-it didn't work...

Macro1 Macro 10am price cut&paste values

Darci, how are you defining 2 minutes from now. Are you using this syntax?
Application.OnTime Now + TimeValue("00:02:00"), "Macro1"

Barrie

Actual time used

Macro1 Macro 10am price cut&paste values

No, i just type in the time that is about to occur so i can test it soon, rather than waiting for 10:00 tomorrow, like now it is 10:35 my time, and i would set it for 10:37, then when 10:37 comes, i wait and see if it has worked or not. hope this helps.. thanks again.
Darci

Re: Actual time used

Macro1 Macro 10am price cut&paste values

I think it might be because you haven't cleared the previous procedure. You can do this by setting the variable "schedule" to false. So, how about using this:
Application.OnTime EarliestTime:=TimeValue("10:55:00"), _
Procedure:="Macro1", Schedule:=False

Let me know if this works for you (this is interesting for me, I've never used this before).

Barrie

Re: Actual time used

Macro1 Macro 10am price cut&paste values
Barrie- it still doesn't start at the designated time.. again thanks foryour input.

Error msg

Macro1 Macro 10am price cut&paste values
Update- the error that i got after manually running the macro from the run macro icon was:
Method 'OnTime' of object '_Application' failed

here is the line of code that is highlighted when i choose debug..
Application.OnTime TimeValue "11:48:00"), "Macro1", , [False]
(it is all supposed to be in one line)
thanks again...

Re: Error msg

Macro1 Macro 10am price cut&paste values

If you copied this from VBA Explorer then I think I see your problem. You are missing the beginning bracket "(" after TimeValue.

Is that it??

Barrie

typo

Macro1 Macro 10am price cut&paste values

Im sorry, the beginning bracket is there in the actual code, but when i pasted it i tried to get it on one line, and it must have gotten erased then. sorry for the misunderstanding.
darci

Learning as I go along

Macro1 Macro 10am price cut&paste values

Is this fun or what??? I think I understand the use of "false" in the syntax. This will, in effect, cancel an on-time procedure you have started. So you don't need the false in the statement. Try this and let me know if it works for you:
Application.OnTime EarliestTime:=TimeValue("15:00:00"), Procedure:="Macro1"

Regards,
Barrie
PS - have you pulled all your hair out (I'm close to it)!!

Re: Learning as I go along

Macro1 Macro 10am price cut&paste values

I have been analyzing this code for three weeks!! but i am excited for a solution. no, taking the false out didn't start the macro, actually, that is what i had before when it didn't work also.. i really appreciate your feedback.. thanks
darci

Re: Learning as I go along

Darci, do me a favour and try your macro after re-booting your computer. The reason I am asking you to do this is because I have no problems running my test macro and you know computers........

Barrie :)

Re: Learning as I go along

Barrie :)
I rebooted the computer, and it still doesn't work(and this computer is only 3 wks old.. ) but i am wondering, what is the difference between entering macro code after right clicking on the xl icon in the menu bar, to the left of the file menu, compared to entering the code after clicking the run macro icon, then selecting my macro1, and then clicking edit. is it the same thing. and maybe if you paste your code into a message, i could compare it to what mine is.. thanks again. darci

Re: Learning as I go along

Darci, here's my code:
Sub OnTime_test()
'Written by Barrie Davidson
Application.OnTime earliesttime:=Now + TimeValue("00:00:05"), procedure:="OnTime_Message"
End Sub
Sub OnTime_Message()
'Written by Barrie Davidson
Dim Message_Time
Message_Time = Format(Now, "mmm d, yyyy HH:MM:SS")
MsgBox (Message_Time)
End Sub

If you right click on the xl icon in the menu bar you will be inputting code that will only be run when the workbook is opened. You should see:
Private Sub Workbook_Open()

End Sub
Anything entered between these two lines will only run when the workbook is first opened.

Hope this helps, let me know.

Regards,
Barrie

Frustration is key to solution

i looked at your code, and it wouldn't just run on its own for me, but when i clicked run macro, it displayed the pop up dialog boxes fine. i am using the following code(from Dave Hawley on 5/18) for my actual - not test..
In the workbook open event. is :
Private Sub Workbook_Open()
Run "StartTimer"
End Sub
Then in the general module i have:
Sub StartTimer()
Application.OnTime TimeValue("14:48:00"), "Macro2"
End Sub

Sub Macro2()
(macro2 code is here, the copy& paste)
Application.OnTime TimeValue("14:46:00"), "StartTimer"
End Sub
it is still not firing on its own..
I also tried and put the private sub workbook paragraph in the normal module, still didn't work. then i tried just leaving out the private sub workbook part, still didn't work. then i tried putting the private sub workbook part in with the rest of the code(on the same page), still didn't work..
Anything that i am overlooking??? thanks Darci

It's getting clearer

The code you have pasted in here will only run when you first open the workbook and Macro2 will only be run at 2:48 PM. It will then run StartTimer at 2:46 PM and the whole thing will start over again. Of course, if you exit Excel, StartTimer will not be run at that time.

The code I pasted will not run on its own. You have to run the macro (select Tools|Macro|Macros|Run) before anything will happen.

Perhaps the best way to tackle this is to go back to the beginning. What are you trying to do with the code?

Regards,
Barrie

basics....

What i am trying to do is cut and paste (values) of a column(only about 15 rows) at 10:00am each day. I want a macro to do this because it must be done precisely at 10am, and this will alleviate the possibility that i am not in the office, or in a meeting, etc. I need the macro to copy and paste these values by itself.. i need it to start by itself, with no human intervention in it ever again, except for during its inception(now...). I hope this is helpful as to what i am trying to do. Thanks again.
When you mentioned that the code only starts starttimer when the workbook is originally opened, why do you suppose it still didn't work when i left it out of the code altogether.?? As far as it only being run at 2:46 and 2:48, (varying with the time of testing), that is what i want it to do. i only need it to copy and paste values at a certain time.
With my limited use of knowledge of macros, i theorized if a macro could be started by an if statement. meaning having a cell in sheet that displays the time, then having an if statement like if time=10:00, then run macro.. i don't think this is possible without implementing it into the code somewhere.. Again, i am trying to get the macro to run on its own.. Thanks again.. Darci

Re: basics....

The code, as posted, should do exactly what you want it to do. A simple question (because I want to make sure we haven't left anything out here), do you keep the computer (and Excel) running all the time? If you shut it down, at the end of the day for example, you will need to make sure you open the spreadsheet before 10:00 am each day.

Does this sound like we're on the right track?

Barrie
(we're almost drinking buddies by now!!)

Re: basics....

This is what i am currently using.....
Sub StartTimer()
Application.OnTime TimeValue("16:37:00"), "Macro2"
End Sub
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 06/06/2001 by ICSF10117A
'

'
Range("B3:B13").Select
Selection.Copy
Range("C3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("B1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Macro 2 ok"
Range("B2").Select
Application.OnTime TimeValue("16:37:00"), "StartTimer"

End Sub

This is not a wide deviation from what i have been using this whole time.. but it doesn't work. it doesn't start on its own i mean.. i change the time to retest it all the time, when the clock turns to that time, i sit and hope that it will run, but it doesn't .. any other suggestions.. thanks. darci

Re: basics....

Darci, the macro won't start on its own. You have to physically launch the macro (from the main menu select Tools|Macro|Macros|Run and select your macro StartTimer). Then leave your computer (and Excel) running and your Macro2 will run each day at 4:37 pm.

Make one change, however, in your Macro 2. Instead of:
Application.OnTime TimeValue("16:37:00"), "StartTimer"
Use:
StartTimer

Barrie (drinking buddy)
Barrie

Try this

Sub StartTimer()
Application.OnTime TimeValue("16:37:00"), "StartTimer"
Macro2
End Sub

Sub Macro2()
Range("B3:B13").Copy
Range("C3").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Range("B1").Value = "Macro 2 ok"
Range("B2").Select
End Sub

This is what i am currently using.....

IT WORKS!!!!!!!!!!!!!!!!!!!!!!

Thank you for all your inputBarrie(& ossie) I have done a few test runs, by changing the time and waiting for it to work, and it works fine.. THANKS SO MUCH!!!! However, now i just have to replicate the results on the actual spreadsheet, hopefully this goes smoothly, but just to be safe, please stay tuned...
One followup question.. is it possible to have the start timer run macro2 at 10am and macro3(simple copy and paste) at 3pm??? just a thought. thanks so much.. darci

Macro3

I'm happy to hear it is working for you. To get macro2 at 10am and macro 3 at 3pm try the following.
In the StartTimer macro change the line to read:
Application.OnTime TimeValue("10:00:00"), "Macro2"
Then in macro2 change:
Application.OnTime TimeValue("16:37:00"), "StartTimer"
To:
Application.OnTime TimeValue("15:00:00"), "Macro3"
Then in macro3 add:
StartTimer
at the end of the macro.

Regards,
Barrie