I think you just needed to undo the change to get the old value.
Private Sub Worksheet_Change(ByVal Target As Range)
'Dim mydatarange As Range
'Set mydatarange = Range("A:A")
'If Intersect(Target, mydatarange) Is Nothing Then Exit Sub
'On Error Resume Next
'If Target.Offset(0, 1) = "" Then
' Intersect(Target, mydatarange).Copy
' Target.Offset(0, 1).PasteSpecial
' Application.CutCopyMode = False
'End If
Dim newVal As Variant, oldValue As Variant
' Only act if cell in column B has been changed
If Intersect(Range("B:B"), Target) Is Nothing Then
Exit Sub
End If
' Turn off events and screen updating
Application.EnableEvents = False
Application.ScreenUpdating = False
' Get new value
newVal = Target.Value
'Undo the change and get the old value
Application.Undo
oldValue = Target.Value
' Copy old value to next blank cell in the row
Cells(Target.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = oldValue
' Redo the change
Target.Value = newVal
' Turn on screen updating and events
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Bookmarks