Date and Timestamp when Excel spreadsheet was last saved, not the workbook overall

Question

Greetings Excel  Experts!

I'm trying to date and timestamp a spreadsheet when it was last saved (not save as) and I'm not able to find a function to help me put that information in a cell. I tried the =NOW() function and discovered I cannot use it on separate spreadsheets within the workbook to reflect when the respective sheet was last modified.

Would someone please guide me in the right direction? 

Thank you in advance for your help...

mcallahan2

Answer 1

Hi mcallahan2,

Thanks for posting in the community, according to your description, you can try the following VBA:

1.    Save the workbook as *.XLSM

2.    Press ALT+F11 to open VBA editor, enter the following event macro for each sheets:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False

Cells(Rows.Count, Columns.Count).Value = Now

Application.EnableEvents = True

End Sub

 

3.    Select “ThisWorkbook” to insert a Module, enter the following code:

Public Function Updatee() As Date

Application.Volatile

Dim w As Worksheet

Set w = Application.Caller.Parent

Updatee = w.Cells(Rows.Count, Columns.Count).Value

End Function

 

4.    Go back to your workbook, use the function: “=Updatee()” to insert last modified timestamp of the current sheet, you can modify the cell to change its date format.

 

Hope the above method can be helpful to you, pleas feel free to post back to let us know the result.

Best Regards,

Arck

Answer 2

Hi mcallahan2,

Sorry for the late response due to out of office.

According to the screenshots you provided, it seems that you did add the macro correctly, to determine whether it is the problem of this specific workbook, could you please create a sample workbook and use the same macro to check the outcome?

If the macro works properly in the newly created workbook, you can try to copy the contents in the problematic workbook to the new workbook.

Best Regards,

Arck