I watched your video on how to enter a macro code that allows automatic data entry timestamps and an updated time stamp for when data is modified, and after following the directions it didnt immediately work for me as it did in the video. However, after closing the document and reopening it, for some reason the time stamps started to show up. The issue i'm having now is that all of the data on the "Summary Sheet" of the Workbook where i've applied the macro is linked to data in other Sheets in the Workbook. So there are no hard entries of data occuring on the Summary Sheet itself for rows E through N. And when I update the data on another sheet, which subsequently updates that linked data on the Summary Sheet, the time stamp on the summary sheet does not update, eventhough data in a particular row has changed. What is the fix for this? I've uploaded a sample workbook with two sheets, a Summary Sheet and a Termite Treatment Data Sheet where the Summary Sheet will pull data from. E15-N15 of the Summary Sheet are the ones that are linked to data in Column B of the the Termite Treatment Data Sheet. Making selections or entering data in Cells B4:B8 of the Termite Treatment Data Sheet Data will generate the values that get linked into the Summary Sheet. This Summary Sheet needs to be set up so that when someone makes any changes to entries in a data sheet that are linked to the Summary Sheet, the "Last Updated" timestamp cell will properly update, and preferably that timestamp cell will highlight yellow. Getting that cell to highlight yellow if it changes is a conditional format that's probably a different topic, but ultimately that's what I need this Summary Sheet to do. I also want to add an acknowledge button at the end of each row, that once pushed or selected, will unhighlight the Last Updated timestamp cell. The idea here is that the person responsible for reviewing the summary sheet is alerted everytime a row has been updated. That's why I want the "Last Updated" cell to highlight yellow when it changes. That person also needs to acknowledge that most recent update, by clearing the highlight by some automatic means at the push of a button or check of a box. This way if that row gets updated again, it will highlight yellow again, and can again be acknowledged to clear the highlight and that process can be repeatedly indefinitely. Again, probably a separate topic, but if you can help me figure those out it would be a hugh help. Priority however, is to get the "Last Updated" timestamp to actually update when linked data is changed.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
The Automatic Timestamp Macro is Not Working Correctly on My File
Discussion
Answers
Please review your logic. You say "Summary!E15:N15 is linked to 'Termite Date'!B:B". That assumption is at the bottom of your trouble. The truth is that 'Termite Date'!B:B is linked to Summary!E15:N15.
That means that whatever is entered on the Termite Data sheet is reflected in the summary and not the other way around. Therefore, when you make an entry in the Data sheet no Change Event occurs in the Summary sheet, and it's the Change Event that drives the time stamp.
Therefore, what you really want is that a time stamp is applied in the Summary when you make a change in the Data. To achieve this with the code you have you need to make two changes, one big and one small. Here is the small change. Replace the existing lines in your code with the ones below.
'Column for the date/time
Set myDateTimeRange = Worksheets("Summary").Range("O" & Target.Row)
'Column for last updated date/time
Set myUpdatedRange = Worksheets("Summary").Range("P" & Target.Row)
Of course, the Worksheet_Event occurs on the Data sheet, not the Summary sheet. That's why waiting for it to happen on the Summary sheet requires infinite patience. Better make the big change already forecast. Move the procedure to the code sheet of the Terminate Data sheet and adjust myTableRange to show where the changes really occur.
This change is likely to give you a headache because of the difference in size between E15:N15 and B:B. I think this has to do with the implicit logic of your workbook which reflects the data in the summary, not the opposite direction. Therefore entries should be made in the data sheet, and that includes the time stamp. You can then import a copy of the time stamp from the data sheet using the same method by which you get the rest of the data.