chrisjwhite90
New Member
- Joined
- Jan 17, 2013
- Messages
- 48
Hello,
I have a completed macro that records price data of certain variables every 30 seconds, it pastes them to a range below, and continues to log these changes in prices by pasting the new variable below the previous.
The code works perfectly, however I need to make a change that I am not sure how to do.
Currently I have start and stop buttons to initiate the recording process, but I'd like to change this to automatically start the macro at a specific time, and end it at a specific time, every day from monday thru friday. I will always have the spreadsheet open, so basically I would like it to automatically start at 6:30 am every weekday, and stop at 5:30pm every weekday.
I'm a novice in VBA, and I've tried to edit my code with no luck. My current code includes the buttons (StartTimer assigned to the Start button, StopTimer for the Stop button), but I'd like to remove them somehow and just automate the process without them. I have this so far:
I don't know exactly where and how to put in a specified start time, record values every increment that I have set, and then end at the specific time. Would I need to set a second timer? Would I need to specify an additional variable to do this? These are the questions I do not know. I would truly appreciate help on this.
(((I have cross posted, I will paste links below)))
I have a completed macro that records price data of certain variables every 30 seconds, it pastes them to a range below, and continues to log these changes in prices by pasting the new variable below the previous.
The code works perfectly, however I need to make a change that I am not sure how to do.
Currently I have start and stop buttons to initiate the recording process, but I'd like to change this to automatically start the macro at a specific time, and end it at a specific time, every day from monday thru friday. I will always have the spreadsheet open, so basically I would like it to automatically start at 6:30 am every weekday, and stop at 5:30pm every weekday.
I'm a novice in VBA, and I've tried to edit my code with no luck. My current code includes the buttons (StartTimer assigned to the Start button, StopTimer for the Stop button), but I'd like to remove them somehow and just automate the process without them. I have this so far:
Code:
[FONT=Courier New]Public RunWhen As Double
Public Const cRunIntervalSeconds = 30 ' 1 second, set 900 for 15 minute intervals
Public Const cRunWhat = "CopyPaste" ' the name of the procedure to run
Private TimerEnabled As Boolean ' This value can only be changed by code in this module
Sub StartTimer()
TimerEnabled = True
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True
End Sub
Sub CopyPaste()
Range("C1") = Range("C1") + 1
Dim Crng As Range, Prng As Range
Set Crng = Worksheets(1).Range("A4:Q4")
Set Prng = Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Crng.Copy
Prng.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
If TimerEnabled Then StartTimer
End Sub
Sub StopTimer()
On Error Resume Next
TimerEnabled = False
Application.OnTime EarliestTime:=Now(), Procedure:=cRunWhat, _
Schedule:=False
End Sub[/FONT]
I don't know exactly where and how to put in a specified start time, record values every increment that I have set, and then end at the specific time. Would I need to set a second timer? Would I need to specify an additional variable to do this? These are the questions I do not know. I would truly appreciate help on this.
(((I have cross posted, I will paste links below)))
Last edited: