Excel Macro Question

Good afternoon All,

I am trying to write a macro that basically will take a snapshot of cell information at different times during the day. IE:

at 9:15 take a snapshot of cell B1,B2,B3 and put in C1,C2,C3
at 10:00 take a snapshot of cell B1,B2,B3 and put in D1,D2,D3

Basically I am trying to take snapshots of stock prices at about 7 different times during the day. I found this code, I just am unsure how to implement it. Any help would greatly appreciated

I found it here: http://www.ozgrid.com/Excel/run-macro-on-time.htm

"Sub MyMacro()

Application.OnTime

TimeValue("15:00:00"), "MyMacro"'YOUR CODEEnd Sub"

I am not sure what my code would be for Row C to equal Row B and would I put this macro in like 7 times, and just change the time?? Just curious. I figure anyone versed in this thinks I am an idiot and can figure this out in about 15 seconds . Have a great weekend, and thanks in advanced.

TPA

 

For the timer, here is an example I have:

Code:

Dim MoniTime As Date

Sub MonitorDDE() 'Add this sub to a button and click it to start the monitor process
'Take a value now and then one every hour
MoniTime = Now + TimeValue("01:00:00")
'If you want specific hours just trim the now + and put your values
'I think you would have to make multiple instances of the Sub for it
'Take the values funtion
Application.OnTime MoniTime, "DDEupd"
End Sub


Sub DDEupd()
Application.DisplayAlerts = False
On Error Resume Next

... put your copy here

'Call the function so that you do it again in one hour
Call MonitorDDE
End Sub

This is not going to work cuz it wont take the first value but I dont feel like solving the problem, I leave it to you.

For the copy, you want to increase the value. I do that by "storing variables in memory" bu writing my indexes (i's and j's) in cells. OK if the code if for you but not clean for an external user.

'Copy the value, suppose what you want to copy is in 2,25
Range(Cells(1, 1), Cells(1,2)) = Cells(2,25)
'Modify the index for next value
Cells(1, 2) = Cells(1, 2) +1

Not sure I use the Cells and Modify correctly (slep like 2-3 hours this night) but you get the idea. Make an index and use it to remember where you are.

Good luck

 

This one is one I use; a simple on/off thing

Code:

Dim MoniTime As Date
Dim MoniTime2 As Date

Sub StartADD()
    MoniTime = TimeValue("09:30:25")
    Application.OnTime MoniTime, "Start"
    MoniTime2 = TimeValue("09:29:55")
    Application.OnTime MoniTime2, "Stop"
    Range(Cells(11, 26), Cells(Range("J5").Value, 26)).Value = "ADD"
End Sub


Sub Start()
    Range("V2") = 0
End Sub

Sub Stop()
    Range("V2") = 1
End Sub

 

You can hard code it

Code:

Sub time915()
Application.OnTime TimeValue("09:15:00"), "copyBtoC" 
End Sub

Sub time1000()
Application.OnTime TimeValue("10:00:00"), "copyCtoD" 
End Sub

Sub copyBtoC()
Range("B1:B3").Select
Selection.Copy
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Sub time1000()
Range("C1:C3").Select
Selection.Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

You should also put the following in 'ThisWorkbook'

Code:

Private Sub Workbook_Open() 
Call time915
Call time1000
End Sub

This code above starts the macro.

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime TimeValue("09:15:00"), "copyBtoC", Schedule:=False
Application.OnTime TimeValue("010:00:00"), "copyCtoD", Schedule:=False
MsgBox "Macro stopped"
End Sub

This code above stops the macro when exiting the sheet, this sometimes does not work well. To stop the ontime macro for sure you should close excel completely.

You can add more times etc.

Hth

 

Thanks for this suggestion. I am new at this and am trying to learn the basics of VB. I spent all yesterday afternoon trying this both ways and couldn’t get it to work. Then this morning I realized that even though it said to do something at a specific time, I still had to actually play the macro and then wait for the time to happen.

I see that the one below is basically a macro running another macro.

Code:

Sub time915()
Application.OnTime TimeValue("09:15:00"), "copyBtoC" 
End Sub

This one is a time mechanism built into the macro itself.

Code:

"Sub MyMacro() Application.OnTime TimeValue("15:00:00"), MyMacro"'YOUR CODEEnd Sub"

I think the first one works better. I just play the macro of the macro and it does it at the specific time. The second one, after I push play, it performs the macro immediately, which I then erase, and then wait till the time comes and it performs it as planned.

Also I have noticed no difference whether this is placed in the “this workbook” code or not. Any reason why?

Code:

Private Sub Workbook_Open() 
Call time915
Call time1000
End Sub

Let me know if I got this right. Any suggestions are greatly appreciated. Thanks

 

It should work when you put that code in 'this workbook' Otherwise just send me a pm and you can email me the sheet and I will have a look at it.

 

I have attached 2 documents here one has the following in the "this worksheet" and one doesn't.

Private Sub Workbook_Open()
Application.OnTime TimeValue("12:32:00"), "typing"
End Sub

I was just wondering what the above did and why there doesn't seem to be any difference as to why it works the same either way. Again thanks so much for the help. The idea of running a macro to run another macro is much more efficient.

 

Here is the second one without the code. Thanks

 

I see what went wrong. The macro in 'this workbook' should start the timing macro. You started the second macro.
So in 'Thisworkbook' it should say:

Private Sub Workbook_Open()
Call playtyping
End Sub

When you put this in the workbook and open the sheet, it triggers the 'playtyping' macro, the macro that works on 12:32. At 12:32 the playtyping macro will call the 'typing' macro.

Hth

 

If I understand you correctly, the call function will automatically play the macro at the specified time without having to push play. I’ve tried it a few times like that and it doesn’t seem to work, but it still works to push play on the Application.OnTime TimeValue("09:54:00"), "typing" code and then have it do the macro at the time specified. Is this the only way to get a macro to play automatically when a condition occurs? Thanks for the help.