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:
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!
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:
Code:
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!