Is this possible? Append refreshing data to another worksheet.

[mozzie789]

Hello all

I have a spreadsheet "data" with cells "a1:e1" that contains numerical values. These numerical values are constantly changing when the spreadsheet is refreshed every minute. All the cells in the range update at the same time - approx every 60 seconds.

Is it possible to "capture" the values in the range "a1:e1" in "data" and send them to another spreadsheet within the workbook named "updated" every 60 seconds? Or, even better, at the time the new data enters the s'sheet "data"?

The "updated" sheet (after 60 secs) would then contain the appended refreshed data to the bottom of the previous set of data - eg. in cells "a2:e2".

In addition, in f1,f2, etc. of "updated", I would like a time stamp of Central Standard Time (Australia) - of the time it was sent to "updated".

This would continue and the data would stop being sent to "updated" when there is no change to cell values "a1:e1" in "data" for more than 180 seconds.

Is this possible, or am I dreaming?

Thanks in advance.

 

[mozzie789]

I see what is happening....it appears as if the values are rereshing all at the same time, however, there is just the slightest time difference between values, hence as each value is updated, there is a new row for each value that is updated. Which is ok.

 

[obiron]

open up the VBA editor.

click in the margin next to the Static t line. You should see a brown dot.

do whatever you need to do to trigger the code.

When the code reaches the brown dot it will stop running and show you the VBA window with the next line to be processed highlighted in yellow. Press F8 and the current line will run and the next line will be highlighted.

At any point you can check what is happening by looking at the worksheets.

The process will either be writing 12 lines at once or one row 12 times. Let us know which it is.

Obiron

 

[mozzie789]

OK Obiron, I'm at work now, but will check later. Thanks.

 

[mozzie789]

Hi Obiron..I stepped through the procedure as instructed and found that it was processing 1 line at a time for each "run through" of the code.

Still puzzling me why 12 lines are coming through when I run procedure in real-time.

 

[obiron]

How are you populating the cells in the monitoring sheet.

If you are deleting data in each cell and then writing a new value then this is ten worksheet_change events. But I can't see why it would create 12 lines unless you have another cell which is not being copied.

If this is the case then you will need to add some validation to the routine to check which cell has been changed and what it is changed to.

If not then I'm stumped!

Obiron

 

[mozzie789]

I'm still stumped here, and we're so close!!

VoG, "Updated" continues to be populated, when it should have (hopefully) stopped updating if there was no change to the appended data after 180 seconds.

And, the "Updated" sheets are still appending several rows of data after each refresh. I was hoping to append 1 row after each refresh.

The code I'm using currently is:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Static t As Date, iRun As Boolean
If Not iRun Or (Now - t) * 86400 < 180 Then
    iRun = True
    t = Now
    Range("A1:E1").Copy Destination:=Sheets("Updated").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Sheets("Updated").Range("F" & Rows.Count).End(xlUp).Offset(1).Value = Now
End If
End Sub

Thanks (again) in advance, this forum is so helpful. I 've tried making small changes to wholesale, large changes to the code without success. With each day, however, I find I'm learning a little more. Thanks again.

 

[obiron]

Thanks for the thanks; it is always nice to know that the effort put in by contributors has been helpful and that you are learning. Soon you will be answering other peoples questions.

You still havn't said how the master sheet is being populated. If you post six cells independantly then the change event is fired six times and will write six lines into the Updated sheet.

See if this makes any difference. The additional clause in the IF statement should cause the write only if the cell that was change was $B$1. If this stops the multiple writes then that is one problem fixed.

Private Sub Worksheet_Change(ByVal Target As Range)
Static t As Date
If (Now - t) / 86400 < 180 and target.Range.Address= "$B$1" Then '<-- change $B$1 to whichever cell is last in the range to be updated.
    t = Now
    Range("A1:E1").Copy Destination:=Sheets("Updated").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Sheets("Updated").Range("F" & Rows.Count).End(xlUp).Offset(1).Value = Now
End If
End Sub

Obiron

 

[mozzie789]

Hi Obiron

I'm getting a Compile Error: Argument not Optional with ".Range" highlighted on the 3rd line of your code.

Does that mean anything to you?

Also, the master sheet is being populated cell by cell, but appears instantaneous to the eye. I think that would be right, as this is what happened when I "stepped into" the code.

I really only need "a1:e1" appended to "Updated" as 1 row per refresh. Not sure if that makes it easier or harder to re-write the code.

Thanks again for sticking with this thread.

 

[obiron]

my bad. handwritten untested code....

Try

Target.Cells(1).Address = "$B$1"

instead.

so to confirm. The change event fires when A1 is populated, then again when B1 is populated and again when C1 is populated etc... which is why it is writing multiple lines.

Assuming that E1 is the last cell to be populated put $E$1 in as the address. The event still fires on each change but will only drop into the write loop if it was E1 that changed.

I think I have also spotted the logic error that means that the event doesn't stop after 3 minutes. Now is the time in seconds. t is also the previous time in seconds. The code is taking one from the other and then dividing by 86400 and seeing if the result is less than 180 which means it actually would not fire until 180 DAYs were passed.

Revised code..

Private Sub Worksheet_Change(ByVal Target As Range)
Static t As Date
If  ((Now - t)< 180 OR t = 0 ) AND Target.Cells(1).Address= "$E$1"    Then '<-- change $B$1 to whichever cell is last in the range to be updated. Check if t is not set because this means it is the first time into the loop.
    t = Now
    Range("A1:E1").Copy Destination:=Sheets("Updated").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Sheets("Updated").Range("F" & Rows.Count).End(xlUp).Offset(1).Value = Now
End If
End Sub

In theory with the code as written the cell A1 could be written at 179.98 seconds and the line would not get written as E1 would be written at 180.01 seconds.

hope this helps

Obiron