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.

 

[obiron]

Yes,

Someone will probably be along shortly with some specific code but you do it by adding some VBA to the worksheet_changed() event of the worksheet. (right click on the tab and choose view code)

Briefly you copy the range that contains the data, reference the new worksheet, find the last used cell and paste the range in just below the cell. You will need to watch out for what to do if you do not have enough space on the worksheet (i.e. you have filled in more than 65500 rows)


Obiron

 

[mozzie789]

Thanks Obiron...I hope someone does come along and submit the code. I'm at the stage where I can understand the code once is written, but don't have the experience to write it "off the bat".

Running out of rows won't be an issue as I expect to only fill around 2,000 rows.

 

[VoG]

Try this: right click the tab of the Data sheet, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
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 Sub

 

[mozzie789]

Brilliant VoG...exactly what I wanted. Thank-you very much!!

 

[mozzie789]

Oops..may have jumped the gun there. That works perfectly except that it won't stop updating. Is there a way I can stop the updating (through code) so that the following holds true, from the original post:

"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."

Thanks...nearly there

 

[VoG]

Possibly...

Private Sub Worksheet_Change(ByVal Target As Range)
Static t As Date
If (Now - t) / 86400 < 180 Then
    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

 

[VoG]

Oops!

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

 

[obiron]

@VoG

I've not tested your code but I'm not sure it would work as t is a local variable and will be cleared each time the procedure runs.

@Mozzie
If you are writing the timestamp to the results sheets you could check the last timestamp and use that as the comparitor. The principal that VoG suggested is fine, just assign t the last value. Note that the script will still run every time the sheet changes, it just won't write the values. You could get the script to set Application.EnableEvents = false the first time is fails to write and then the script will not be activated, but it is probably not worth the hassle.

Obiron

 

[mozzie789]

Guys...looking very close, as it does almost exactly what I was after.

However, it reproduces the "row" of information 12 times. All the 12 "rows" are the same (correct information), but I only want the row returned once.

What is going on there?

Thanks again for your continued support. This would take me forever to work out.