Need ideas for history tracking fro specific cells

[dmxcasper2]

Hi all,
I need some ideas for tracking the historical changing values.

Problem Statement:
Column H and I will be revised multiple times throughout daily maintenance of this sheet, however I would like to be able to keep historical snapshots of what the previous information was in these columns before I changed it.

I would like to keep the history on a separate sheet. I'm OK with copying the entire row of information and moving it to the separate sheet, but I would like to automate this process.

Any suggestions or ideas are welcomed. Thank you in advance.

 

[mumps]

Do you change both columns on any one row or can you change just one column and not the other at any one time? For example, if you change cell H103 will you also change cell I103? Are the changes made manually or are they the result of a formula?

 

[JLGWhiz]

You can use the Worksheet_Change event to do that. It could be a pretty busy operation if you make a lot of changes in those columns, because it will record the change immediately upon entry of a different value in either column.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim o As String, n As String
lr = ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    If Not Intersect(Target, Range("H2:I" & lr)) Is Nothing Then
        n = Target.Value
        Application.Undo
        o = Target.Value
        Range(Cells(Target.Row, 2), Cells(Target.Row, Columns.Count).End(xlToLeft)).Copy
        Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2) = Now 'Edit sheet name
        Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(, 1).PasteSpecial xlPasteValues
        Target = n
    End If
Application.EnableEvents = True
End Sub

BTW, this puts a date/time stamp in column A of the destination sheet.

 

[dmxcasper2]

Do you change both columns on any one row or can you change just one column and not the other at any one time? For example, if you change cell H103 will you also change cell I103? Are the changes made manually or are they the result of a formula?

I would be changing both columns at a time (Column H & I), one row at time.
The changes are made manual.

 

[mumps]

Before I work on something, have you tried the macro suggested by JLGWhiz to see if it works for you?

 

[dmxcasper2]

Sorry, I'm a newbie when it comes to VBA.

What attributes do I have to revise to the code for it to work if the following needs to be accomplished.

Sheet called "MASTER" is the screenshot above. The data will be copied into sheet called "T History".

 

[JLGWhiz]

The code below has been modified to reflect the destination sheet name. To use the code, Right click on the name tab of sheet "Master", then click 'View Code' in the pop up menu. When the vb Editor screen appears, copy and paste the code into the large code pane, then make sure your file is saved as a macro enabled workbook (.xlsm). Close the vb editor and your code will execute when you make a change in column H or I.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim n As String
lr = ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    If Not Intersect(Target, Range("H2:I" & lr)) Is Nothing Then
        n = Target.Value
        Application.Undo        
        Range(Cells(Target.Row, 2), Cells(Target.Row, Columns.Count).End(xlToLeft)).Copy
        Sheets("T History").Cells(Rows.Count, 1).End(xlUp)(2) = Now 'Edit sheet name
        Sheets("T History").Cells(Rows.Count, 1).End(xlUp).Offset(, 1).PasteSpecial xlPasteValues
        Target = n
    End If
Application.EnableEvents = True
End Sub

Be sure the sheet name in the code is exactly as shown on the sheet name tab to avoid a Subscript out of Range error message.

 

[dmxcasper2]

The code below has been modified to reflect the destination sheet name. To use the code, Right click on the name tab of sheet "Master", then click 'View Code' in the pop up menu. When the vb Editor screen appears, copy and paste the code into the large code pane, then make sure your file is saved as a macro enabled workbook (.xlsm). Close the vb editor and your code will execute when you make a change in column H or I.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim n As String
lr = ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    If Not Intersect(Target, Range("H2:I" & lr)) Is Nothing Then
        n = Target.Value
        Application.Undo        
        Range(Cells(Target.Row, 2), Cells(Target.Row, Columns.Count).End(xlToLeft)).Copy
        Sheets("T History").Cells(Rows.Count, 1).End(xlUp)(2) = Now 'Edit sheet name
        Sheets("T History").Cells(Rows.Count, 1).End(xlUp).Offset(, 1).PasteSpecial xlPasteValues
        Target = n
    End If
Application.EnableEvents = True
End Sub

Be sure the sheet name in the code is exactly as shown on the sheet name tab to avoid a Subscript out of Range error message.

JLGWhiz,

Thank you. The macro works perfectly.

One last question:

Is it possible to modify the vba code to only be triggered if both values in Column H and I are changed to execute the copying operation?

 

[JLGWhiz]

Is it possible to modify the vba code to only be triggered if both values in Column H and I are changed to execute the copying operation?

I cannot think of a way to determine if a change has occured in both columns at the same time that would allow the change event to trigger. That event has to operate on the immediate change. But by putting the date time stamp in the destination sheet, you can can tell if both columns were changed at the same time. If both columns were always blank prior to entries being made, then a check could be made to see if both columns had data when a change was made and only report those instances, but if there is existing data in the columns, vba cannot distinguish whether the second column was changed when a change occurs in the other column. Not trying to confuse you, just trying to explain the logic.