Audit trail macro to handle insertion or row or column and the cut and paste functionality

[zephyriswing]

So the short and skinny of it is that I have pieced this macro together and in no way claiming to be even a novice at Excel and vba. Anyways, I am trying to get this audit trail macro to be able to handle two conditions.
1st is the insertion of a row or a column. The macro faults out with the insertion of a row or column, and I thought I had it licked at one point but it would still crash out even with the log actually logging the inserted row. So I decided to strip it backing to working conditions. Basically, what is happening is that the code is breaking upon not having a previous cell value, but I cannot for the life of me figure out a way to appropriately handle it. Also, the macro handles each cell change, so I would rather the macro stop at the first conditional cell error and just log in the audit trail section row or cell was inserted.
2nd is correcting the cut and paste functionality that seems to be busted. Only the first part (the cut) is logged and then the paste function is not working. I am thinking this is mainly due to the application.undo feature but wouldn't bet my life on it. It seems to still work with Copy and paste.

I am trying to create either solution without using worksheet_selectionchange sub if at all possible. PW is "password" and any help is appreciated.

[cytop]

Ignore ....

[TMS]

I think the Undo destroys the Target range. So, maybe try it like this:

Please Login or Register  to view this content.

[TMS]

The code does now appear to work. However, if you insert a row or a column, it has a lot of cells to check ... particularly if you insert a column ... and takes a while to execute. Not sure how bad it would get if you were to insert several columns. You might want to limit it by taking the intersection of the Target Range and the Used Range.

[AlphaFrog]

Here's and alternative method. Instead of using Application.Undo, it uses a duplicate data sheet called Data Entry Mirror. It compares the changes on the Data Entry sheet to it's mirror sheet, logs the differences, and updates the mirror sheet.

First make a copy of the Data Entry sheet and name it Data Entry Mirror. You could hide this sheet if you wanted.

It also logs if a rows or columns have been inserted or deleted.

Please Login or Register  to view this content.

[zephyriswing]

Thanks for your input, it was really helpful!

[zephyriswing]

Wow, I never thought about solving it that way. AlphaFrog That solution's pretty awesome. It executes really quickly too. I appreciate the help and am really grateful!

[TMS]

You're welcome. Thanks for the rep.