VBA - Track Changes in Separate Worksheet

sektr

New Member
Joined
Mar 22, 2019
Messages
1
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:

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!
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,598
.
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.

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

Well-known Member
Joined
Aug 31, 2016
Messages
3,598
.
Or if you are interested ... I can provide a completed project using another macro.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,348
Messages
5,528,184
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top