VBA - Track Changes in Separate Worksheet

[sektr]

I am trying to set up code so that when I make changes to a cell in a particular worksheet, named "Data", the changes are logged in my "Log" worksheet in a table with the following columns:

1. Worksheet Name
2. Item Number (the data from the cell in the corresponding row in column A of the Data sheet)
3. Column Name (text string from row 1 of the Data sheet)
4. Old Value
5. New Value
6. Time

I found some sample code online that I thought would work, but not sure if I'm on the right track in adapting it. Here is what I have so far, but I'm not sure how to define the other variables or if this structure will even work:

Private Sub Worksheet_Change(ByVal Target As Range)


'Dimensions
Dim SerialNumber As Single
Dim ColumnName As String
Dim oldValue As Double
Dim newValue As Double
Dim dtmTime As Date


'Paramaters
dtmTime = Now
newValue = Target.Value




Dim WorkSheetName As String
    WorkSheetName = "Data"



'If ActiveSheet.Name <> "Data" Then
'    Application.EnableEvents = False




Sheets("Log").Range("A" & rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name
Sheets("Log").Range("A" & rows.Count).End(xlUp).Offset(0, 1).Value = SerialNumber
Sheets("Log").Range("A" & rows.Count).End(xlUp).Offset(0, 2).Value = ColumnName
Sheets("Log").Range("A" & rows.Count).End(xlUp).Offset(0, 3).Value = oldValue
Sheets("Log").Range("A" & rows.Count).End(xlUp).Offset(0, 4).Value = newValue
Sheets("Log").Range("A" & rows.Count).End(xlUp).Offset(0, 5).Value = dtmTime




Sheets("Log").Columns("A:F").AutoFit
'    Application.EnableEvents = True
'End If
    
End Sub

I have the ending If statement disabled right now as when it is enabled the macro does not run, although it was included in the code I sampled this from.

Super new to VBA so any help is really appreciated!

 

[Logit]

.
The edited version functions but it is not capturing the SERIAL NUMBER, COLUMN NAME, or OLD VALUE.

The macro is missing the code to capture the above. Re-check the resource website.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)




'Dimensions
Dim SerialNumber As Single
Dim ColumnName As String
Dim oldValue As Double
Dim newValue As String  '<----- changed to STRING.
Dim dtmTime As Date


'Paramaters
dtmTime = Now
newValue = Target.Value


Dim WorkSheetName As String
    WorkSheetName = "Data"


'If ActiveSheet.Name <> "Data" Then
'    Application.EnableEvents = False


Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name
Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = SerialNumber
Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = ColumnName
Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = oldValue
Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = newValue
Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(0, 5).Value = dtmTime


Sheets("Log").Columns("A:F").AutoFit
'    Application.EnableEvents = True
'End If
    
End Sub

 

[Logit]

.
Or if you are interested ... I can provide a completed project using another macro.