PDA

View Full Version : Solved: Cell value capture



maninjapan
06-16-2010, 05:49 AM
Id like to write a macro that will capture the value of a cell and post it to the next empty cell in sheet 2 every X minutes.

I have RTD time server to provide me with the time in excel.

(if theres a thread that covers this just a link would fine)

Thanks

mdmackillop
06-16-2010, 07:52 AM
Look at OnTime in VBA help, and also here (http://www.cpearson.com/excel/OnTime.aspx)

maninjapan
06-18-2010, 04:23 AM
Ive been able to come up with the following ( a combination from my recent threads, thanks people). It does what it is expected to do, however I have come across another problem. The cell that it is supposed to be copying and pasting is actually a formula, so it actually copies and pastes the formula, not the value that I see in the cell. The value is what I actually want to record. how do I adjust the following to achieve this?
Public RunWhen As Double
Public Const cRunIntervalSeconds = 60 'seconds
Public Const cRunWhat = "TheSub" ' the name of the procedure to run
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=False
End Sub
Sub TheSub()
''''''''''''''''''''''''
With Worksheets("Data")

LastRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
Worksheets("Sheet1").Range("B20").Copy .Range("A" & LastRow)
End With

''''''''''''''''''''''''
StartTimer ' Reschedule the procedure
End Sub

mdmackillop
06-18-2010, 04:29 AM
Worksheets("Sheet1").Range("B20").Copy
.Range("A" & LastRow) .PasteSpecial Paste:=xlPasteValues

maninjapan
06-18-2010, 08:23 AM
thanks again, I added it to the sub, but get an error saying 'object required' when I run it. It cut and pasted ok before the last change.

Sub TheSub()
''''''''''''''''''''''''
With Worksheets("Data")

LastRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
Worksheets("Sheet1").Range("B20").Copy.Range("A" & LastRow).PasteSpecial Paste:=xlPasteValues
End With

''''''''''''''''''''''''
StartTimer ' Reschedule the procedure
End Sub

mdmackillop
06-18-2010, 08:53 AM
The Copy and Paste must be on two lines as posted

maninjapan
06-18-2010, 08:56 AM
Thanks, all seems to be working now.