Saving updated cell values in new cells before it refreshes.

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.
- karanmahajan
  Copper Contributor
  Sep 26, 2024
  JKPieterse
  
  is there a way to save the updated values on different cells each time with a timestamp next to it?
  - JKPieterse
    Silver Contributor
    Sep 26, 2024
    karanmahajan Depends. Please explain your exact requirements.
- Marco_NL
  Copper Contributor
  Sep 05, 2022
  JKPieterse
  
  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
- LisaT760
  Copper Contributor
  Mar 02, 2022
  JKPieterse
  How do I change the macro if I have data in cells B5, C5 and D5 as well?
  - 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

Forum Discussion

Saving updated cell values in new cells before it refreshes.

Resources