VBA Code to Track Changes in Excel

[that_one_girl]

Hi Again,

I have a code (copied below) that copies data from a master sheet, to one of two other sheets within the same workbook. This is running in a 2016 Excel workbook that will have multiple people from multiple departments working on it, logging in via an intranet.

What I need now, is a code to track the changes that are made in any of the sheets as soon as it's opened. I only need changes tracked, not cell selections...

Criteria needed would be:

• Track WHO made changes
• Track WHEN WHO made changes
• Track what column and row WHO made changes to and WHEN (prefer readout be D6 instead of 6,4)
• Track WHAT WHO changed WHEN

Example would be:
Joe S. changed D6 in ALL RECORDS worksheet on 1/9/17 from "CURRENT" to "ARCHIVE"
Jill F. changed C3 in ARCHIVED worksheet on 2/8/17 from "Lazarra, A" to "Lazzara, A"

Here's my current code for transferring the data.
I'm not sure on how I would apply a second code (insert a new module?) so also need to know if I would add it to the part after "End Sub" below, or if I need to make a new module for the entire worksheet please

Private Sub Workbook_Open()
Dim i, LastRow
LastRow = Sheets("ALL RECORDS").Range("A" & Rows.Count).End(xlUp).Row
Sheets("ACTIVE").Range("A2:L60869").ClearContents
Sheets("ARCHIVED").Range("A2:L60869").ClearContents
For i = 2 To LastRow
If Sheets("ALL RECORDS").Cells(i, "J").Value = "CURRENT" Then
Sheets("ALL RECORDS").Cells(i, "J").EntireRow.Copy Destination:=Sheets("ACTIVE").Range("A" & Rows.Count).End(xlUp).Offset(1)
ElseIf Sheets("ALL RECORDS").Cells(i, "J").Value = "ARCHIVE" Then
Sheets("ALL RECORDS").Cells(i, "J").EntireRow.Copy Destination:=Sheets("ARCHIVED").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next
End Sub

 

[SQUIDD]

Hi

Did you know, that when you share the workbook under the review tab.
Excel will track all you changes for you?
It will also let you handle what happens if 2 users try the same cell.

If it helps?

Dave

 

[that_one_girl]

Hi - yes, but if you have a VBA or MACRO running, then the track changes feature is unavailable as a feature. So I can't track changes this way - this is why I was asking for VBA coding that would do it........but thanks!

Hi

Did you know, that when you share the workbook under the review tab.
Excel will track all you changes for you?
It will also let you handle what happens if 2 users try the same cell.

If it helps?

Dave