Forum Discussion

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

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 ...
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.
LisaT760's avatar
LisaT760
Copper Contributor

JKPieterse 

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

JKPieterse's avatar
JKPieterse
Silver Contributor
Mar 02, 2022

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
  • OJBridger's avatar
    OJBridger
    Copper Contributor
    May 23, 2022

    JKPieterse 

     

    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.

Resources