How to display last modified date in EACH worksheet

good morning, all - i have searched for over half an hour now on this and finally decided to reach out to the community. the various responses to the posts i found in re to this were not specifically addressing what is currently happening in my test worksheet. hopefully, someone can shed some lite.

trying to have a cell in each worksheet of a workbook display the last time that specific worksheet was edited. currently, i have the following code on the module level for the three sheets {fyi - each corresponding =SavedDate#() is in cell C1}:

Function SavedDate() As Date
Application.Volatile
SavedDate = ActiveWorkbook.BuiltinDocumentProperties.Item(12)
End Function

--

Function SavedDate1() As Date
Application.Volatile
SavedDate1 = ActiveWorkbook.BuiltinDocumentProperties.Item(12)
End Function

--

Function SavedDate2() As Date
Application.Volatile
SavedDate2 = ActiveWorkbook.BuiltinDocumentProperties.Item(12)
End Function
*******

the first test using those three fcn statements worked perfectly where the cell "C1" correctly displayed the last edit after saving/reopening the workbook a couple of times. of course, ALL three modified dates were identical {pls note - i have the cells formatted to show the date AND time to test if the macro is actually working, so that has not been an issue thus far as i can plainly see it showing, say, 4/12/13 3:45am or 4/12/13 3:32am and so forth}.

however, when i went to add the SECOND part of each macro on the sheet level {e.g., using right click/View Code}, here is where the problem occurred. since i specifically want the sheets to be independent of each other, i cannot just use the three macros from above. i thought--based on searching various sites and playing around w/the code--that the following things added at the sheet level would take care of the three dates being independent and only changing in a specific sheet if that sheet was edited. alas, i keep getting these run-time errors ==> Method 'Range' of object'_Worksheet' failed

i did not think i had to do anything extra to each sheet's code than what i am pasting below {so, did not think any special naming of add'l variables or defining things and so forth would be necessary, correct or...???}:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Range("C1") = SavedDate
End Sub

--

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Range("C1") = SavedDate1
End Sub

--

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Range("C1") = SavedDate2
End Sub
********

and when i debug, of course it just goes to the one line of code "ActiveSheet.Range(..." in each. right now i have this second part in comment mode ['] and still trying to search and play, but i defer to the experts here.

thx in advance for any assistance. i do hope i provided sufficient information for the dilemma since this is my official first posting. i thought this was going to be a slam dunk but i had to laugh at myself that it has already turned into an hour+ . go figure.

best-

MLA

**********UPDATE********

if anyone is still awake, i found a slight variation to the sheet level coding. how odd - now i no longer get the run-time error msg, BUT this code ends up making ALL dates/times in EACH worksheet identical, thus defeating the purpose of independent modified date calcs. for each worksheet i will add the code here but i guess i will continue to search until i am too tired to think. <:o) thx again if anyone can assist.

here is the slight variation in code @ sheet level i just found/edited for my testing:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Sh.Range("C1").Value = SavedDate
End Sub

----

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Sh.Range("C1").Value = SavedDate1
End Sub

----

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Sh.Range("C1").Value = SavedDate2
End Sub

****

a final thought - i do not think this should matter but i will name that "C1" range in each separate worksheet just to see if that will make the dates change independently of each other if three different ranged names are used...probably not, i guess. does not hurt to test @ this point, tho.

okay - thx again, anyone, for your help. best-

MLA

Code in Thisworkbook object

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If TypeOf Sh Is Worksheet Then
        Application.EnableEvents = False
        Sh.Range("C1") = ThisWorkbook.BuiltinDocumentProperties(12)
        Application.EnableEvents = True
    End If
    
End Sub

C1 will update when a change occurs.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code in [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here