How to record the last value in excel

Sunil

Your question doesn't say which cell changes (but I've assumed C5 since you speak of M5 saving the last value) or how it changes "automatically".

If it's changed manually, you can use the Change event macro (behind Sheet1) which I've added to the revised file attached (and commented for your understanding). If you type a new value in cells C5, c6 or C7, in column M it will record the "Previous value  (from column C)" (as per the heading for the green cells there):

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CurVal As Long

If Target.CountLarge = 1 And Not Intersect(Target, Range("C5:C7")) Is Nothing Then
    ' prevent re-triggering this macro
    Application.EnableEvents = False
    With Target
        ' get the current value
        CurVal = .Value
        'restore previous value
        Application.Undo
        'save previous to column M
        .Offset(0, 10).Value = .Value
        'restore previous value
        .Value = CurVal
   End With
End If 
Application.EnableEvents = True

End Sub

If your "automatic" update is by SQL updates or similar, I suggest you store the current value, perhaps in the hidden rows I:L), then copy that into M5 say each time the update occurs and also overwrite the (hidden) current value cell.

Hope this helps.