How to record live data on separate sheet every 10 minutes?

[Jamford]

Hello

I have a worksheet with live data of a currency value relative to a USD that updates every 10 minutes in cell e2, and a live clock in cell d2. I would like to record both of these values every 10 minutes in another worksheet, so I have an account of the daily volatility of the value. What is the best way to do this?

Thank you for the help.

 

[Chihiro]

I assume you already have macro that updates E2 & D2?

Then just add a code line that also records the time value and currency value elsewhere at the same time.

Something like....

Dim iRow As Long
Dim dws As Worksheet
Dim sws As Worksheet

Set dws = ThisWorkbook.Sheets("DestinationSheetName")
Set sws = ThisWorkbook.Sheets("SourceSheetName")

iRow = dws.Range("D" & Rows.Count).End(xlUp).Row + 1

sws.Range("D2:E2").Copy dws.Cells(iRow, 4)

Edit: Changed reference column for iRow to "D"

 

[Jamford]

Hi Chihiro

Thank you for the reply, but I am still having a little trouble. Every time the clock updates I get a new recording of the clock and data on my desired worksheet. Unfortunately when the live data in cell e2 updates from the web it changes the value of the previously recorded data.

For example, the value was $423.72 at 1:34 pm. When the data refreshed from the web the value at 1:34 pm changed to the updated value. Is there a way for me keep the previously recorded value the same once the data updates from the web?

I have uploaded my workbook in case that helps make my issue clear.

 

[Chihiro]

Ah, thought it was value obtained from Macro. Here's the version that only pastes values.

Dim iRow As Long
Dim dws As Worksheet
Dim sws As Worksheet

Set dws = ThisWorkbook.Sheets("Recorded Data")
Set sws = ThisWorkbook.Sheets("Bitcoin Data")

iRow = dws.Range("D" & Rows.Count).End(xlUp).Row + 1

sws.Range("D2:E2").Copy
dws.Cells(iRow, 4).PasteSpecial xlPasteValues

End Sub

 

[tooyiw]

interesting

 

[vletm]

tooyiw
You should reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

After You have reread it,
You will know what and how to continue.
Ps. This thread belongs to Jamford

 

[pipahaha]

I'm looking forward to it

word counter​