Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Lock Cell Value

0

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

Answer
Discuss

Answers

0
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.

Updated

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()

Range("A1:A10").Copy
Range("C1:C10").PasteSpecial

Application.CutCopyMode = False
Range("A1").Select

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.

Discuss

Discussion

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