• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Jamford

New Member
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

Excel Ninja
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....
Code:
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"
 
Last edited:

Jamford

New Member
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.
 

Attachments

Chihiro

Excel Ninja
Ah, thought it was value obtained from Macro. Here's the version that only pastes values.
Code:
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
 

vletm

Excel Ninja
tooyiw
You should reread Forum Rules
After You have reread it,
You will know what and how to continue.
Ps. This thread belongs to Jamford
 
Top