VBA for Save old values

Hi Gargamel and welcome to the Forum

You can achieve that using the Worksheet_Change event (if you'e careful!).

Put this code behind the sheet you need it to operate on. I've added comments to explain how it works:

Private Sub Worksheet_Change(ByVal Target As Range)

' Quit if change isn't in range
If Intersect(Target, Range("A1:A50")) Is Nothing Then Exit Sub
'Otherwise...
Application.EnableEvents = False ' prevent second trigger of this code
myChange = Target.Value ' grab new value
Application.Undo 'restore old value
Target.Offset(0, 1) = Target.Value 'copy old to column B
Target.Value = myChange 'restore new value to column A
Application.EnableEvents = True ' restore events

End Sub

If however you have cells with formula (as your discussion indicates) then provided they refer to cells in the same sheet, use this macro (which uses the Calculate event) instead. I've added comments and shown the bits in bold which might be changed to cover a bigger range or place values in a different column:

Private Sub Worksheet_Calculate()

Dim HistoryRg As Range, OldVals As Variant

Set HistoryRg = Range("A1:A50") 'set the range (in a single column) whose history will be recorded. 

Application.EnableEvents = False 'prevent a recalculation during the running of this code
    Application.Undo 'undo the change then store values to an array
    OldVals = Application.Transpose(Application.Transpose(HistoryRg))
    Application.Undo ' restore the changed values
    HistoryRg.Offset(0, 1).Value = OldVals ' paste the pre-change values to the offset column
Application.EnableEvents = True 'restore events

End Sub

Hope this works for you.

Thankyou for your answer.

İ have a probelm with your solution. Exa. in A1 , i cant use any formula. like ( E1+D1). I cant add any formula in  column A