Solved: Cell value capture

[maninjapan]

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]

Look at OnTime in VBA help, and also here

[maninjapan]

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?
[VBA]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
[/VBA]

[mdmackillop]

[VBA]
Worksheets("Sheet1").Range("B20").Copy
.Range("A" & LastRow) .PasteSpecial Paste:=xlPasteValues
[/VBA]

[maninjapan]

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.
[VBA]
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[/VBA]

[mdmackillop]

The Copy and Paste must be on two lines as posted

[maninjapan]

Thanks, all seems to be working now.