Store new line of data when values change in another cell(s) - change value macro

46 views Asked by At

I have a spreadsheet that has an add-in that is constantly feeding in data. The data changes from time to time. I need a macro that will create a new line item storing this data with a time stamp each time the value in any one of the 3 cells changes.

In cells J3, L3, N3 are the values that are feeding in live data that will change. Whenever the value in J3, L3, or N3 changes I want the values in those cells to copy and paste values in a new line item in cells B, D, F with a time stamp added in column A.

I'm a novice so go easy on me. Below is the code, which doesn't work because it only updates one column if one of the values changes, but I want all 3 values to get logged regardless if its only the data in J3, L3, N3 that change:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$J$3" Then
SPOT1 = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row + 1
Sheets("Sheet1").Range("B" & SPOT1).Value = 
Sheets("Sheet1").Range("J3").Value
Sheets("Sheet1").Range("A" & SPOT2) = "=Now()"

End If

If Target.Address = "$L$3" Then
SPOT2 = Sheets("Sheet1").Cells(Rows.Count, "D").End(xlUp).Row + 1
Sheets("Sheet1").Range("D" & SPOT2).Value = 
Sheets("Sheet1").Range("L3").Value
Sheets("Sheet1").Range("A" & SPOT2) = "=Now()"

End If

If Target.Address = "$N$3" Then
Spot3 = Sheets("Sheet1").Cells(Rows.Count, "F").End(xlUp).Row + 1
Sheets("Sheet1").Range("F" & Spot3).Value = 
Sheets("Sheet1").Range("N3").Value
Sheets("Sheet1").Range("A" & Spot3) = "=Now()"

End If

End Sub

Any help is appreciated. Thank you.

0

There are 0 answers