Lock Cell Value


I have a spreadsheet consisting of one column and ten rows. The spreadsheet also has a cell containing a timer in the format hh:mm:ss.

The values in the single column are updated 'live' over a 30 minute period.

My problem is I would like to automatically 'Lock/Freeze' the values in the column at a certain time (e.g after 10 minutes ) during the 30 minute period they are being updated.

I would appreciate any help on how to do this



Selected Answer

All of this depends on how the data is being updated.

If you just want the data to "freeze" in place, then you need to edit whatever is causig the data to update in the first place, such as a macro.

However, if you just want to copy the data to another worksheet or part of the same worksheet after 10 minutes, then you need a small macro to copy that data after the prescribed time.


Here is a sample macro that will copy values from row A1:A10 to C1:C10 after 10 minutes

Sub timer()

Application.OnTime Now + TimeValue("00:10:00"), "copy_code"

End Sub
Sub copy_code()


Application.CutCopyMode = False

End Sub

You can read more about this macro at our tutorial on running a macro so long after a workbook has been opened.

To run this specific macro, just run theĀ timer module.



Hi don,
Thanks for the reply. My VBA is a bit rusty (few years ago ).
Would just like to copy the cell values to say an adjacent column at a certain time e.g say after 10 mins.
Just wondering if you could please point me in the right directionon on best way to do this with some code examples if possible.
Cardano (rep: 2) Feb 3, '17 at 9:51 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login