Copying real time data in excel using VBA

[asquare007]

Hello - I am trying to copy real time data in subsequent rows whenever the data gets updated. I am using the following VBA code. This updates irrespective of any change. I only want it to change when the time stamp in cell A2 is updated. Any suggestions on how can I update this code?



Private Sub Worksheet_Calculate()
capturerow = 2
currow = Range("A65536").End(xlUp).Row
Cells(currow + 1, 1) = Cells(capturerow, 1)
Cells(currow + 1, 2) = Cells(capturerow, 2)
End Sub


Cheers

[p45cal]

try (untested here):
Private Sub Worksheet_Calculate()
Static DTStamp
If DTStamp <> Range("A2") Then
capturerow = 2
currow = Range("A65536").End(xlUp).Row + 1
Cells(currow + 1, 1) = Cells(capturerow, 1)
Cells(currow + 1, 2) = Cells(capturerow, 2)
DTStamp = Range("A2")
End If
End Sub
Note that I've guessed you might need the +1.

[大灰狼1976]

Hi asquare!
Welcome to vbax forum.

Private Sub Worksheet_Calculate()
capturerow = 2
currow = Range("A65536").End(xlUp).Row
Application.EnableEvents = False 'prevent ripple effects
Cells(currow + 1, 1) = Cells(capturerow, 1)
Cells(currow + 1, 2) = Cells(capturerow, 2)
Application.EnableEvents = True
End Sub