Feb 22 2023 08:36 AM
Feb 22 2023 08:36 AM
I want to create a macro so that I can see the last modified date for each worksheet not the entire workbook*. How do I go about this? I tried to modify the last modified formula so that it is specific to a sheet and then I would create a new one for each sheet that I want to see as per below and then i did =LastModifiedk() in a cell. However, I get a "VALUE!" error. Thanks!
Feb 22 2023 08:43 AM
Document Properties only apply to the workbook as a whole, not to individual worksheets.
It would be possible to write VBA to register when the user last changed the value of any cell in a worksheet. Where would you like this information to be stored? In a cell on the worksheet itself, or on a summary sheet, or ...?
Feb 22 2023 08:52 AM
Feb 22 2023 11:45 AM
See the attached sample workbook. The code is in the ThisWorkbook module (under Microsoft Excel Objects in the Project Explorer pane of the Visual Basic Editor).
You'll have to allow macros when you open it, obviously.
Feb 23 2023 02:01 AM
Thanks for sending that over. I am very new to VBA. Would I need to change where it says "Summary" to the sheet name where I want to display it? And how do I pull the data in for each sheet?
Thanks for your help!
Feb 23 2023 03:25 AM - edited Feb 23 2023 03:25 AM
Yes. Change the name Summary in the following line to the name you want to use.
Set wsh = Worksheets("Summary")
The code will run automatically whenever you change a cell on a worksheet.
Feb 23 2023 05:47 AM
Feb 23 2023 06:26 AM
Copy the code from the ThisWorkbook module in the sample workbook to the ThisWorkbook module of your own workbook.
Save your workbook as a macro-enabled workbook (*.xlsm), if it isn't a macro-enabled workbook already.
Make sure that you allow macros when you open it.
Feb 23 2023 07:18 AM
Feb 23 2023 07:28 AM
Have you tested editing some cells in various sheets? The code should take care of everything automatically.
Let me know if it doesn't do what you want.
Feb 23 2023 08:54 AM
Feb 23 2023 11:54 AM
If you want a timestamp for the majority of sheets, specify the ones you don't want to have a timestamp.
If you want a timestamp for a minority of sheets, specify the ones you do want to have a timestamp.
Please explain in detail where you want the timestamps to appear.