Record changes on another sheet by copying row

[excelnovice264]

Hi All,

I've scoured the forum and the internet to try and find a solution to my problem but can't quite find exactly what I need. I'm convinced this is a relatively simple problem to solve.

I have a worksheet with 30+ columns and 200 rows and what I would like to do is whenever I make a change, copy the row with the cell I am changing to another sheet and highlight the cell that I have changed from with the previous data. I could probably record a Macro to do this but it would be very clunky and would be much more efficient using a simple VBA solution.

The only additional piece of data that I would like to add to the row that wouldn't be present in the original sheet is to add the date and time in a new cell at the end and the user perhaps if there's a way of doing that, as many users have access to this sheet.

Thanks in advance

[TMS]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

[excelnovice264]

I've created a dummy set of data to show what I am looking for. The 1st work sheet labelled data is the master data sheet where all changes are made. The 2nd worksheet labelled changes is where i would like to record the changes with the addition of the date/time and user.

[TMS]

Something like:


This code goes in the Data Sheet code module

Please Login or Register  to view this content.

[excelnovice264]

Thank you very much for your help, this is almost spot on. One issue I have remaining is that it doesn't seem to record changes if the data is changed en masse by dragging across cells, if i want to click and drag to fill in multiple cells it only records the first change and none of the others, presumably because I haven't press the enter button after every change, is there a way to resolve this so that it updates the 2nd sheet on change rather than on enter press

Does this solution effectively disable the undo button?

[excelnovice264]

Ok so I've been testing this today and apart from the issue where you have to record a key press to register the change it also seems to add the date and user stamps within the last 2 columns of the data table on a larger data set rather than attach it to the end and I cannot see why whatsoever. Any help with this would be much appreciated.