CODEWITHSUNDEEP
vbaexcelms-office
Chandru
Chandru

Reputation: 107

Excel to copy realtime data and create a chart out of it

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.

Upvotes: 2

Views: 6315

Answers (2)

gssi
gssi

Reputation: 5071

Why are you moving the real time data point? Use a variable to identify the computed location for each successive entry.[You can use the OFFSET and COUNTA functions to re-compute the cell location of the variable.] Use the Worksheet.Change event to generate timestamp value in an adjacent cell.

Upvotes: 0

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

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.

Upvotes: 1

Related Questions