Saving updated cell values in new cells before it refreshes.

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.