How to copy cells every 20 seconds?

[dikken20]

Hello,

Example:
A1 = 10 (it's actually a calculated value, meaning, it changes like every second, but for this example let's say it's 10)
B1 = 20
C1 = 30
D1 = 33

I would like the cells copied every 20 seconds, at that sequence:
First copy D1 gets C1, then C1 gets B1 m finally B1 gets A1 ( A1 will changes by itself using a third party data source),
meaning:
A1 = Variable but let's say 15
B1 = 10
C1 = 20
D1 = 30

Then after another 20 seconds, changed again:
A1 = Variable
B1 = 15
C1 = 10
D1 = 20


And so on...
How do I do that in VBA?

Thanks!

 

[offthelip]

How you do this depends on what is updating the worksheet and whether it triggers the worksheet change event or not. Is regular enough to count 20 of the updates and copy the value every 20th update?

 

[dikken20]

How you do this depends on what is updating the worksheet and whether it triggers the worksheet change event or not. Is regular enough to count 20 of the updates and copy the value every 20th update?

As for now, I'm importing a CSV that is automatic downloaded every sec and imported to my sheet.
In the near future I'm going to change the way I'm importing to RTD technology.
I import a huge amount of data (online trading statistics) and I would like to check part of the data every 20 seconds to see the difference between the values of each 20 seconds...

 

[offthelip]

You didn't actually answer either of my questions, however assuming the update does trigger the worksheet change event and assuming that it is regular enough just to count the updates, this code should do what you want.:

Public countr
Private Sub Worksheet_Change(ByVal Target As Range)

If countr > 20 Then
rlim = 100
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
If lastrow > rlim Then lastrow = rlim
inarr = Range(Cells(1, 1), Cells(rlim - 1, 1))
Application.EnableEvents = False
Range(Cells(2, 1), Cells(rlim, 1)) = inarr
Application.EnableEvents = True
countr = 1
Else
countr = countr + 1
End If

End Sub

rlim is just a limit on the number of rows to copy.

 

[dikken20]

You didn't actually answer either of my questions, however assuming the update does trigger the worksheet change event and assuming that it is regular enough just to count the updates, this code should do what you want.:

Public countr
Private Sub Worksheet_Change(ByVal Target As Range)

If countr > 20 Then
rlim = 100
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
If lastrow > rlim Then lastrow = rlim
inarr = Range(Cells(1, 1), Cells(rlim - 1, 1))
Application.EnableEvents = False
Range(Cells(2, 1), Cells(rlim, 1)) = inarr
Application.EnableEvents = True
countr = 1
Else
countr = countr + 1
End If

End Sub

rlim is just a limit on the number of rows to copy.

Hey, Thanks for the code
Let me answer again in a different way, since I couldn't see any change in my sheet using the code (I copied it to "This workbook" and also in a new module, maybe I need to copy the code somewhere else?)

The trigger I'm looking for is - every 20 seconds. meaning,
if now time is 14:00:00, then it will run the process I specified above (the copy/paste process),
then on 14:00:20 - once again, process will run again..
next.. on 14:00:40.... 14:01:00...14:01:20.. and so on.

Does that answer your original question?

 

[dikken20]

Oh one more thing...Just for clarification:

Regarding the "Worksheet change" as a trigger - it's not what i'm looking for as my sheet contains dozens of thousands of cells of data that change every second (in fact.. more like every mili-seconds), therefore the trigger is the time, meaning every 20 secods..

 

[offthelip]

The worksheet change event code needs to be in the worksheet code module, to open this; right click on the tab of the worksheet you want to monitor and select "view code". Paste this code into the worksheet module. I have updated it to compare the times to get a time close to 20 seconds between updates. This does depend on the automatic updates to your worksheet triggering the worksheet change event. This may or may not happen depending on how and what is up-dating your worksheet. The best thing is to try it and see.

Public countr
Dim delta As Double

Private Sub Worksheet_Change(ByVal Target As Range)
If countr = 0 Or countr = "" Then
countr = Time()
End If
On Error GoTo errh
delta = 24 * 60 * (Time() - countr)
If delta > 0.3333 Then
rlim = 100
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
If lastrow > rlim Then lastrow = rlim
inarr = Range(Cells(1, 1), Cells(rlim - 1, 1))
Application.EnableEvents = False
Range(Cells(2, 1), Cells(rlim, 1)) = inarr
Application.EnableEvents = True
countr = Time()
End If
Exit Sub
errh:
countr = Time()
Application.EnableEvents = True

End Sub