Feb 08 2021 10:38 PM
Feb 08 2021 10:38 PM
I need help. I need to record the changes in a range of cells in a single column to a new sheet with the time of the change and next change on a new cell.
record changes on Sheet1, Column A, Cell A5:A1005
save changes on Sheet2 from cell A5 to row 5 in Sheet2, starting with B5 and next change on C5 with time as the header and same with other cells like from cell A6 to row 6.
Please help me, I am stuck on this problem for a long time.
If additional information is needed kindly let me know.
Feb 09 2021 04:42 AM
But the value in B5 on the new sheet could refer to another time than the value in B6, B7 etc.
Can you tell us what the new sheet should actually look like?
Feb 09 2021 05:58 AM
Since you do not provide any further data, such as Excel version, operating system, workbook (without sensitive data), I will send you this task and solution that you can adapt to your project.
I have a folder with 53 tables (one for each week)
- The tables are each from A1: Q550, the data are in A9: Q550
- One line = one data record.
- I would now like that if someone changes something in a line, the date, time and name of the person who made the change is in this line in column R.
- The logging should take place automatically without switching on a macro.
It is not possible to record with macro, you have to write in the VBE (Alt + F11).
I don't like your desired solution, because only the last change in the entire data set is logged here.
Better to log every change.
Private Sub Workbook_SheetChange (ByVal Sh As Object, ByVal Target As Range) Dim ErsteFreieZeile As Long If Target.Count> 1 Then Exit Sub If Sh.Name = "Protocol" Then Exit Sub If Intersect (Target, Sh.Range ("A9: Q550")) Is Nothing Then Exit Sub With Sheets ("Protocol") FirstFreeLine = .Cells (Rows.Count, 1) .End (xlUp) .Row + 1 .Cells (ErsteFreieZeile, 1) = Sh.Name .Cells (ErsteFreieZeile, 2) = Target.Address (0, 0) .Cells (ErsteFreieZeile, 3) = Target.Value .Cells (ErsteFreieZeile, 4) = Date .Cells (ErsteFreieZeile, 5) = Time .Cells (ErsteFreieZeile, 6) = Environ ("username") End With End Sub
- Right click on your VBA project, properties, protection
- Lock project for display
- Assign a password and keep it safe ...
Now a change log is created in the background, which nobody can see at first.
If you want to see it, there are two options:
Private Sub Workbook_Open () If Environ ("username") = "Nikolino" Then 'adjust! Worksheets ("Protocol"). Visible = xlSheetVisible End If End Sub
Here you still have to change your Windows login name (probably "Oscar").
The log is only visible to you.
Private Sub Workbook_BeforeClose (Cancel As Boolean) Worksheets ("Protocol"). Visible = xlSheetVeryHidden End Sub
Ensures that it is made invisible again when the file is closed.
One possible result in the "Protocol" sheet:
Here you can now easily search for the change that annoys you, e.g. using an auto filter ...
Everything freshly fished from the internet
I would be happy to know if I could help.
I wish you continued success with Excel
I know I don't know anything (Socrates)
* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.
Feb 09 2021 06:10 AM
I have shared an attachment for example. Hope it helps..
In the new sheet, the details for the cells on sheets1 should be saved horizontally. as on the old sheet, all data are stored in a column.
If time cannot be placed commonly for all changes, then maybe an option if changes after every min could be recorded.
Feb 09 2021 06:31 AM
Thank you @Nikolino,
I am sorry I did not share any reference data earlier. I believe you would be able to help me out. I have shared a file for reference.
I would like to record the changes that happen after every min for every row(user). The final goal is to plot the changes in price/values in a chart and refresh the data once every week.
High hopes with you
Feb 09 2021 07:19 AM
The attached example will record the values of column A on Sheet1 every minute, whether a change has occurred or not.
It is a macro-enabled workbook, so you have to allow macros for it to work.
There is code in a standard module and in the ThisWorkbook module.
Feb 09 2021 07:32 AM
How do I do it?
what if it's on the standard excel? I can change it.