Keep Previous Value of Cell

[ajcormac]

Hi there,

I have two columns, let's call them Event Date (Column A) and Previous Event Date (Column B).
I would like to implement some VBA code (or use formulas if necessary) that automatically keeps the previous value of Event Date and retains it in the adjacent cell for Previous Event Date.

For example, if A3 = Jan-03 and I update A3 to Jan-04, B3 would then show Jan-03 as it is the previous value of the cell.
I would like this code to work for a whole column of cells, not just a single cell.


I'm open to any & all ideas and appreciate the help. Thanks.

 

[JLGWhiz]

This procedure will record your entry temporarily, reverse post the entry, copy the original value to the adjacent cell in column B, then repost the new value. The user will probably not notice all these events, because it happens instantaneously. The code should be copied to the worksheet code module of the sheet where you want the data to be recorded. To access the code module, right click the sheet name tab, then click 'View Code' in the pop up menu. The code is triggered automatically by any change to the worksheet, but will only execute the copy action if the change occurs in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cng As String
Application.EnableEvents = False
If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Range("A:A"), Target) Is Nothing Then
        cng = Target.Value
        Application.Undo
        Target.Offset(0, 1) = Target.Value
        Target = cng
    End If
Application.EnableEvents = True
End Sub

 

[VoG]

Cross posted Keeping the Previous Value of a Cell

Please read Excelguru Help Site - A message to forum cross posters