xneurosis
02-17-2013, 10:50 PM
Hello,
I currently have a macro that works perfectly. It takes a specific row, and copy and pastes it below at a specific time interval, and continues to do so to keep a database of changing values. Originally, this was initiated manually and stopped manually. Here is the thread where the finished code (and workbook) is at the bottom: http://www.excelforum.com/excel-programming-vba-macros/894126-vba-copy-and-paste-to-infinite-range.html?p=3097298
Now, I would like help automating this macro to start at TimeValue("06:30:00'"), and end at TimeValue ("16:30:00"), every monday thru friday.
The problem is, I have gotten all mixed up with my time interval and start time of the macro, and for some reason, it only starts the macro, copies one value, and then stops, which tells me I've messed something up.
Ultimately, I would love for this to run constantly and automatically without having to touch it. My PC is always on, and excel is always open, so that won't be a problem. The issue is having it run fluently without my manual supervision. Here is my code so far, please let me know what I've made a mistake on!!
Public RunWhen As Double
Public Const cRunIntervalSeconds = 10
Public Const cRunWhat = "CopyPaste"
Sub DataAutoRun()
RunWhen = TimeValue("06:30:00") + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime RunWhen, "CopyPaste"
If Time >= TimeSerial(16, 30, 0) Then
Application.OnTime RunWhen, "CopyPaste", , False
End If
End Sub
Sub CopyPaste()
Range("C1") = Range("C1") + 1
Dim Crng As Range, Prng As Range
Set Crng = Worksheets(1).Range("A3:Q3")
Set Prng = Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Crng.Copy
Prng.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End Sub
I currently have a macro that works perfectly. It takes a specific row, and copy and pastes it below at a specific time interval, and continues to do so to keep a database of changing values. Originally, this was initiated manually and stopped manually. Here is the thread where the finished code (and workbook) is at the bottom: http://www.excelforum.com/excel-programming-vba-macros/894126-vba-copy-and-paste-to-infinite-range.html?p=3097298
Now, I would like help automating this macro to start at TimeValue("06:30:00'"), and end at TimeValue ("16:30:00"), every monday thru friday.
The problem is, I have gotten all mixed up with my time interval and start time of the macro, and for some reason, it only starts the macro, copies one value, and then stops, which tells me I've messed something up.
Ultimately, I would love for this to run constantly and automatically without having to touch it. My PC is always on, and excel is always open, so that won't be a problem. The issue is having it run fluently without my manual supervision. Here is my code so far, please let me know what I've made a mistake on!!
Public RunWhen As Double
Public Const cRunIntervalSeconds = 10
Public Const cRunWhat = "CopyPaste"
Sub DataAutoRun()
RunWhen = TimeValue("06:30:00") + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime RunWhen, "CopyPaste"
If Time >= TimeSerial(16, 30, 0) Then
Application.OnTime RunWhen, "CopyPaste", , False
End If
End Sub
Sub CopyPaste()
Range("C1") = Range("C1") + 1
Dim Crng As Range, Prng As Range
Set Crng = Worksheets(1).Range("A3:Q3")
Set Prng = Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Crng.Copy
Prng.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End Sub