Forum Discussion

Copper Contributor

Feb 22, 2023

How can I create a last modified macro for each worksheet?

Hi All, 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 ...

excel

Formulas and Functions

Macros and VBA

jburrell

Copper Contributor

Feb 22, 2023

Hi Hans,

Ah ok, that sounds more of what I am looking for. Yes, I would like to show if a user has amended any cell in a worksheet. I want to store the information on a summary sheet.

Thanks,
Jamie

HansVogelaar

MVP

Feb 22, 2023

jburrell

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.

LastModified.xlsm20 KB

jburrell
Copper Contributor
Feb 23, 2023
HansVogelaar

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!
- HansVogelaar
  MVP
  Feb 23, 2023
  jburrell
  
  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.
  - jburrell
    Copper Contributor
    Feb 23, 2023
    Thanks Hans. And when I copy the code to my own workshop, how do I get the code to run? Do I need to a formula into the cell I want it to appear? Thanks again (sorry for my lack of knowledge)