2

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Chandru
  • 107
  • 2
  • 9
  • The `Worksheet.Change` event is your first port of call: http://msdn.microsoft.com/en-us/library/office/ff839775.aspx – Skip Intro May 23 '13 at 13:49

2 Answers2

1

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.

Our Man in Bananas
  • 5,809
  • 21
  • 91
  • 148
  • Hi thanks for the code but this line " worksheets("Sheet2").Range("c7").Value = Worksheets("Sheet1").Range("A6").Value " will it not copy to the same cell again in the next run ? – Chandru May 23 '13 at 15:48
  • @Chandru yes, you need to determine the next row down using `Cells(Rows.Count, 1).End(xlUp).Row` (returns the last used row) and you increment by 1. – Our Man in Bananas May 23 '13 at 15:54
  • Hello Philip its not working. Its copying the value to the same row :( – Chandru May 24 '13 at 06:25
  • @Chandru: I have modified my code, copy it again, then put a breakpoint on the line where it puts in the value, then when it stops you can see the row number and find out what's happening – Our Man in Bananas May 24 '13 at 08:19
  • I used a counter varaible i. Worksheets("Sheet2").Range("A1").Offset(i, 0).Value = Worksheets("Sheet1").Range("A6").Value This line worked . Thank you very much for your help – Chandru May 24 '13 at 11:25
0

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.

gssi
  • 5,043
  • 5
  • 23
  • 23