Excel to copy data in real time and create a graphic

advertisements

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.