Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

How to record the last value in excel



I am trying to record the last value in excel.

For example:

Let say, The cell value is 100, than the value change to 200 automatically. So, the last value of 100 to record in different column cell in excel.

Same I am trying to record in cell M5 in attached excel.

Please help.




Selected Answer


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
        '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.


Hi John,
I think the file which you have attached is not updated excel sheet.
Can I request you to please share the updated excel sheet.
SunilA (rep: 40) Jan 11, '22 at 12:05 am
Sunil;. You're right. Please see my revised Answer/ file (which now records previous values from C5:C7)
John_Ru (rep: 2867) Jan 11, '22 at 3:26 am
Did that work then?
John_Ru (rep: 2867) Jan 13, '22 at 7:53 am
Yes, Thanks John
SunilA (rep: 40) Jan 13, '22 at 12:19 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login