CODEWITHSUNDEEP
vbaexcel
Niva
Niva

Reputation: 298

Last Save Date and Time VBA for a specific worksheet

Hi there i have come across of last save options for a workbook, whereby the code allows to track the date and time of it being last modified and displayed in a cell .I was wondering if vba could allow to track the data and time of it being last modified on a specific worksheet, lets say Sheet1. So everytime changes has been made and saved in sheet 1, it would reflect the time and date saved of that sheet only. Here is the code i have for the workbook so far, tried adding .Sheets("Sheet 1") to the code but it tracks the time i visited the page and not that of i edited. These are the codes in my workbook.

   Private Sub Workbook_Open()
  Call starttheClock
End Sub

Sub Workbooky()
ActiveWindow.ScrollRow = 1
End Sub

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

    If ActiveSheet.Name = "Index" Then Exit Sub

    Application.EnableEvents = 0

    i = ActiveSheet.Index

    With Sheets("Index")

        .Cells(i, 1) = ActiveSheet.Name
        .Cells(i, 2) = Now

    End With

    Application.EnableEvents = 1

End Sub

Upvotes: 0

Views: 2054

Answers (2)

Davesexcel
Davesexcel

Reputation: 6984

Just to add a bit more confusion to the above answer, if you do have a sheet named "Index" and want to have a date that is added to a sheet every time there is a change to it, possibly a code that will go into the workbook module. Then you will have only one code to check whenever any of the worksheets have a change.

This is where the workbook module is located and the code belongs there.

enter image description here

This assumes you have a sheet named "Index", name it whatever you want once you get it going properly. The actual sheet name and the sheet name in the code have to match exactly though.

enter image description here

Here is the code that will go in the workbook module.Copy and paste it into the Workbook Module

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

    If ActiveSheet.Name = "Index" Then Exit Sub

    Application.EnableEvents = 0

    i = ActiveSheet.Index

    With Sheets("Index")

        .Cells(i, 1) = ActiveSheet.Name
        .Cells(i, 2) = Now

    End With

    Application.EnableEvents = 1

End Sub

Upvotes: 1

Isu
Isu

Reputation: 127

You can't save a worksheet. Always you have to save the whole workbook. That is the reason why it comes up with errors.

If you want to know the exact time and date you edited each sheet, you can find as per below.

http://www.ozgrid.com/forum/showthread.php?t=46624

Private Sub Worksheet_Change(ByVal Target As Range) 
    Sheets("Index").Range("B2") = Now 
End Sub

Upvotes: 1

Related Questions