Excel – How to Keep History of Data Changes in Excel Workbooks

microsoft excel

good afternoon,

I am working on an excel sheet where I have a table of data. This data represents several metrics that my department wants to keep track on. Whenever someone wants to populate the table with new data, the previous data in the cells are lost.

How can someone create a separate sheet where you can store all the values of that table automatically? I mean, when a change is done in one of the table cell, the new value to be automatically stored in another table that will keep all the values(old and new) of the cells? I tried the "track changes" but I'm not sure whether I like this way so much.

Does anyone know a more efficient way to do this? Through a macro for example?

Thank you!

Best Answer

(I'm on mobile so I can't provide a very fleshed out answer right now.)

I have written code to exactly this. My intent was to track all changes on a critical sheet edited by multiple users. If there was a dispute about where data came from, I could review the log. Here are VBA pieces that will come in handy.

Worksheet_Change event will fire every time the worksheet is changed.

If Not Intersect(Target, Range("A1:G12")) Is Nothing will tell you whether or not the cell(s) that changed are within some range you care about.

It's faster to store the values you want to log into an array and them set some range on your log sheet to be equal to that array as opposed to setting each cell on the log sheet individually.

Take a stab and see how far you get. I can be a little more verbose tomorrow.

Next Day Edit

Here's some code that will watch the range A1:G12 on whatever sheet has the code in it. If r is the row that was changed, then the code will copy everything from Ar:Gr into a sheet whose code name is shtLog. (Code name is the name shown in VBA, not the name on the tab you see in Excel.) This should get you moving in the right direction.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Integer
    Dim c As Integer
    Dim arr(1 To 1, 1 To 12)
    If Not Intersect(Target, Range("A1:G12")) Is Nothing Then
        r = Target.Row
        For c = 1 To 12
            arr(1, c) = Cells(r, c).Value
        Next
        With shtLog
            .Range(.Cells(.UsedRange.Rows.Count + 1, 1), .Cells(.UsedRange.Rows.Count + 1, 12)) = arr
        End With
    End If
End Sub

Get started

Make sure the developer ribbon is showing, then switch to the VBA editor (Alt-F11).
Read through this page to get an appreciation of the overall process; some of the examples may be very close to what you want (e.g. 'Automatically Creating Workbook Files from Worksheet Data').

Reading a cell

dim myVar1 as string
myVar1 = Sheets("Sheet1").Range("A1")

That will allow to read from any particular cell on any particular sheet.

Aggregating cell values

Dim myvar2 As Integer
myvar2 = Application.WorksheetFunction.Sum(Sheets("Sheet1").Range("B4:B6"))

That will allow you to use any of the standard worksheet functions, on any range of cells.

Writing to a cell

Sheets("Sheet2").Range("A1") = myVar1

(simply the reverse of reading)

Connecting the macro to a button on a sheet

After you create your macro, add a button to your master sheet wherever makes the most sense, following this guide.

Good luck!

EDIT 1:

Writing based on offset

To write to a particular cell based on a date offset, you could use the DateDiff function to get the distance to offset, e.g.:

Dim startDate As Date
Dim currDate as Date
Dim dateOffset As Integer

startDate = #10/21/2011#
currDate = Sheets("Summary Sheet").Range("F3")
dateOffset = DateDiff("d", startDate, currDate)

Then, you can write to a particular cell in the same fashion you already are:

ActiveSheet.Range("A1").Offset(0, dateOffset) = Total1
ActiveSheet.Range("A1").Offset(0, dateOffset+1) = Total2

etc

EDIT 2:

Finding a date

Based on your comment: you can find a date in another sheet as follows:

Dim selectedDate As String
Dim rangeFound As Range
selectedDate = ActiveCell.Value
Set rangeFound = Sheet2.Cells.Find(CDate(selectedDate))

Obviously you'd change Sheet2 to whatever you wanted. That will give you the address of the cell containing the date on the summary sheet. If you use rangeFound.Address you will get a value similar to $D$9. If you use rangeFound.Row and rangeFound.Column you will get the actual row and column numbers. Note that you also have the address of the active cell through the same principle (e.g. ActiveCell.Row). From there, you can copy values using something like

If Not (rangeFound Is Nothing) Then
    ActiveCell.Offset(0, 1) = rangeFound.Offset(0, 1)
    ActiveCell.Offset(0, 2) = rangeFound.Offset(0, 2)
End If