Need ideas for history tracking fro specific cells

dmxcasper2

Board Regular
Joined
Mar 21, 2012
Messages
62
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,255
Office Version
2013
Platform
Windows
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.
Code:
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.
 
Last edited:

dmxcasper2

Board Regular
Joined
Mar 21, 2012
Messages
62
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
Before I work on something, have you tried the macro suggested by JLGWhiz to see if it works for you?
 

dmxcasper2

Board Regular
Joined
Mar 21, 2012
Messages
62
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,255
Office Version
2013
Platform
Windows
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.
Code:
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.
 
Last edited:

dmxcasper2

Board Regular
Joined
Mar 21, 2012
Messages
62
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.
Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,255
Office Version
2013
Platform
Windows
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.
 

Forum statistics

Threads
1,081,574
Messages
5,359,707
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top