Last Modified Date for anything CHANGED within a sheet (VBA)

Copper Contributor



I have a workbook with about 60 individual sheets, 10 of which are "overview" sheets returning specific data from the other 50 sheets.  I would like one cell in each of the 50 sheets to display the date and time that the last CHANGE was made to that sheet, not last date opened, printed or saved. I would like it to update the moment any cell within the sheet has been altered.


Is this possible

1 Reply


To avoid individual procedures for each of the 50 sheets, create an event procedure in the ThisWorkbook module.

This example sets the date and time in Z1. You can easily specify another cell in the code.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Select Case Sh.Name
        ' Skip the overview sheets
        Case "Overview1", "Overview2" ' ...
            ' Skip these
        Case Else
            Application.EnableEvents = False
            Sh.Range("Z1").Value = Now
            Application.EnableEvents = True
    End Select
End Sub