I am very new to VBA programming.
I have a excel sheet where real time data is populated to a cell say for example Sheet1.A6 everysecond.
I am in need of a macro which will run every 1 minute to copy the Sheet1.A6 value to another sheet along with timestamp like value in Sheet2.A and timestamp in Sheet2.B .
Sheet2 Output will be like
Value * Time
23.1 * 11:00 AM
22.5 * 11:01 AM
22.6 * 11:02 AM
Thanks for your help.
you want some code to run every minute.
You should be able to do this with the Application.OnTime function.
Here are a couple of references and examples:
First create your procedure which will copy the cell to the destination sheet.
Then in a normal module you can call it like this:
Sub SetTimeForCopy() dim nextTime as date nextTime = Now + TimeValue("00:01:00") Application.OnTime TimeToRun, "CopyValue" End Sub
copy the value over like this:
Sub CopyValue() application.Calculate worksheets("Sheet2").Range("c" & Cells(Rows.Count, 1).End(xlUp).Row+1).Value = Worksheets("Sheet1").Range("A6").Value Call SetTimeForCopy' call the schedule code again End Sub
NOTICE that in CopyValue the SetTimeForCopy procedure is called which will schedule the next copy.