Freezing Values
 

I have a worksheet that received dynamic feeds. I want to preserve the value
at a certain point in time, such as 4 PM, without having to do a Copy, Paste
Special as Values every day. Is this possible? Thanks.

Freezing Values
 

Chuck,

You could use a macro with the ontime method. See an example below, which stores the values from
cells A1:E1 in the first available cells below the last data stored - it also stored the date and
time in column F. Run StartIt to initiate the process, and keep it open forever.... You can
manually store the data by running PreserveValues without the OnTime line included.

HTH,
Bernie
MS Excel MVP

Dim NextTime As Date

Sub StartIt()
NextTime = TimeValue("16:00:00")
Application.OnTime NextTime, "PreserveValues"
End Sub

Sub PreserveValues()
Dim mySht As Worksheet
NextTime = TimeValue("16:00:00")
Set mySht = ActiveWorkbook.Sheets("Sheet Name")
With mySht.Cells(Rows.Count, 1).End(xlUp)(2)
.Resize(1, 5).Value = mySht.Range("A1:E1").Value
With .Offset(0, 5)
.Value = Now()
.NumberFormat = "mmm dd, yyyy hh:mm:ss"
End With
End With
Application.OnTime NextTime, "PreserveValues"
End Sub

Sub StopIt()
Application.OnTime NextTime, "PreserveValues", schedule:=False
End Sub






"Chuck" wrote in message
...
I have a worksheet that received dynamic feeds. I want to preserve the value
at a certain point in time, such as 4 PM, without having to do a Copy, Paste
Special as Values every day. Is this possible? Thanks.