Saving updated cell values in new cells before it refreshes.

[Emil Larsson]

Saving updated cell values in new cells before it refreshes.

I have a spreadsheet that updates values in specific cells taken from the web, every hour, using web query. I want to be able to save each value before it updates/refreshes and add it last in a table to be able to use it for statistics.

For example, after one day I would have 24 different values saved in a table. See the picture below, the value 10 in "Updating value" should be saved in the cell under 7 before it updates. Then continuing to add values further down for each hour. (This is not my real spreadsheet just an example)

Would really appreciate if someone could help me out!

//Emil

[#2]

How is this update triggered precisely?

[#3]

It is triggered by a web query that refreshes every 60 minutes. The cell value for A5 is changed every 60 minutes. I want to be able to save every value in other cells that don't refresh.

[#4 · Accepted solution]

1. Make sure Excel calculates when cell A5 changes by placing a formula like =A5 in any cell on that sheet
2. Right-click the sheet's tab and choose "View code"
3. paste this code:
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Me.Range("M" & Me.Rows.Count).End(xlUp).Offset(1).Value = Me.Range("A5").Value
Application.EnableEvents = True
End Sub
Mind you, this also will copy the value of cell A5 if you manually hit F9 or if something else causes a calculation in our file.

[#5]

Thanks! Worked flawlessly! :D

[#6]

You're welcome!

[#7]

@Jan Karel Pieterse 

How do I change the macro if I have data in cells B5, C5 and D5 as well?

[#8]

@LisaT760 That would become something like this:

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Me.Range("M" & Me.Rows.Count).End(xlUp).Offset(1).Resize(,4).Value = Me.Range("A5:D5").Value
    Application.EnableEvents = True
End Sub

[#9]

@Jan Karel Pieterse 

Hi! 

I'm utilising your bit of code and it works great, however I want to write the values to another sheet (like a data dump style thing)

I tried modifying your code from this:

Me.Range("M" & Me.Rows.Count).End(xlUp).Offset(1).Resize(,4).Value = Me.Range("A5:D5").Value

to this:

Me.Range("OtherWorksheet!M" & Me.Rows.Count).End(xlUp).Offset(1).Resize(,4).Value = Me.Range("A5:D5").Value

Trying to get it to reference and point to another sheet, however I can't get it to work. Any ideas? It gives me compile errors and things like that.

[#10]

@Jan Karel Pieterse 

Thank you for the code! It works very well!

I've adapted it to copy two columns when the calculation changes, but is there a way that it puts it in the next free column when the result changes again? To keep track of all the changes made?

Hope you can help!

Kind regards,
Marco

Private Sub Worksheet_Calculate()

Application.EnableEvents = False

Me.Range("AA" & Me.Rows.Count).End(xlUp).Value = Me.Range("H:H").Value

Me.Range("AB" & Me.Rows.Count).End(xlUp).Value = Me.Range("I:I").Value

Application.EnableEvents = True

End Sub