Is this possible? Append refreshing data to another worksheet.

mozzie789

New Member
Joined
May 19, 2008
Messages
17
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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.
 
Upvote 0
Try this: right click the tab of the Data sheet, select View Code and paste in

Code:
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
 
Upvote 0
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
 
Upvote 0
Possibly...

Code:
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
 
Upvote 0
Oops!

Rich (BB 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
 
Upvote 0
@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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top