Create and Excel workbook/worksheet change log -- 2
Budget: $30 – $250 AUD
I have an excel that I want to have a live change log file created and stored in a separate file.
The log should submit on only particular column edits like C:C, J:J, K:K
When a change is made to any of the columns on the worksheet a record should be created for the entire row in a separate workbook. The Changelog workbook will be closed and be being used for Power BI reporting on entries from the file.
I did have a working script that created a log into the existing workbook on a new sheet. But this recently stopped and I am not sure why as I changed nothing.
Script running was, issue is it stopped running on the row "Me.Rows(Target.Row).Copy .Cells(Rw, 1)" this morning for some reason and 2nd main issue is I need it to create the log into a closed separate workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim dtmTime As Date
Dim Rw As Long
If Intersect(Target, Range("C:C,J:J,K:K")) Is Nothing Then Exit Sub
dtmTime=Now()
With ThisWorkbook.Sheets("LOGS")
Rw= .Cell(.Row.Count, 1).End(x1Up).Row + 1
Me.Rows(Target.Row).Copy .Cells(Rw, 1)
.Cells(Rw, 21)=dtmTime
End WIth
End Sub
The log should submit on only particular column edits like C:C, J:J, K:K
When a change is made to any of the columns on the worksheet a record should be created for the entire row in a separate workbook. The Changelog workbook will be closed and be being used for Power BI reporting on entries from the file.
I did have a working script that created a log into the existing workbook on a new sheet. But this recently stopped and I am not sure why as I changed nothing.
Script running was, issue is it stopped running on the row "Me.Rows(Target.Row).Copy .Cells(Rw, 1)" this morning for some reason and 2nd main issue is I need it to create the log into a closed separate workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim dtmTime As Date
Dim Rw As Long
If Intersect(Target, Range("C:C,J:J,K:K")) Is Nothing Then Exit Sub
dtmTime=Now()
With ThisWorkbook.Sheets("LOGS")
Rw= .Cell(.Row.Count, 1).End(x1Up).Row + 1
Me.Rows(Target.Row).Copy .Cells(Rw, 1)
.Cells(Rw, 21)=dtmTime
End WIth
End Sub