Forum Discussion

Emil Larsson's avatar
Emil Larsson
Copper Contributor
Jan 13, 2018
Solved

Saving updated cell values in new cells before it refreshes.

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)

 

https://fud.community.services.support.microsoft.com/Fud/FileDownloadHandler.ashx?fid=4b883d69-9d54-4375-9106-f0ceb2632930

 

Would really appreciate if someone could help me out!

 

//Emil

excel
  • JKPieterse's avatar
    JKPieterse
    Jan 16, 2018
    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.

11 Replies

Sort By
    • Emil Larsson's avatar
      Emil Larsson
      Copper Contributor
      Jan 15, 2018

      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.

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Jan 16, 2018
        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.

Resources