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

To:  mcallahan2

re:  time stamp

Enter the =Now() function on the worksheet.

Right-click the cell and choose Copy

Right-click the cell and choose Paste Values.

You must stll save the workbook.

The above can be accomplished automatically using VBA.

It would be most efficient, if the change area on each sheet was identified.

You would still have to save the workbook.

'---

The free Excel workbook "Professional_Compare" compares every cell Or

each row in two worksheets - choice of compare type.

Includesutilities: "Clean Data, Quick Uniques, Descriptive Statistics, Benford Distribution".

Download (no ads) from:  http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

Answer 3

Hi mcallahan2,

I’m writing this reply to follow up this case, may I know have you checked my last reply? Please let us know whether it is helpful to you, your response could also help those who have the same question in this community.

Best Regards,

Arck

Answer 4

H Arck -

I get the dreaded #NAME? error. I've saved the workbook with the .xlsm extension, verified the code as entered correctly. Any ideas?

Answer 5

Arck -

Here are my screenshots:

This is reflective of both Sheet 1 & Sheet 2