Auto backup with date at end of day AND enter last modified & last user in a cell

[Hyp40]

Hi I have two problems. I need my spreadsheet to automatically make a backup copy of itself each day, with the file name to include the date. Is that even possible?

Also, when any cell in a row is changed, I would like a cell at the end of that row to show the now() date, time and the user. I have tried: but it changes the time of ALL the rows above, up to the one that has changed.

Public Function Lastmodified(c As Range)


Lastmodified = Now()


End Function

Thank you in advance for any help

 

[Yongle]

when any cell in a row is changed, I would like a cell at the end  of that row to show the now() date, time and the user.  I have tried:    but it changes the time of ALL the rows above, up to the one that has  changed.

You cannot do that by formula - the formula updates when the sheet is calculated

Place code below in the SHEET module
- amend to what you want to happen
- currently monitor columns A to Y and places the value in Z
- cell in column Z is updated with every edit to any cell in the same row in columns A to Y

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:Y")) Is Nothing Then Range("Z" & Target.Row) = Now
End Sub

 

[Yongle]

I need my spreadsheet to automatically make a backup copy of itself each day, with the file name to include the date. Is that even possible?

Try something like this
- it saves to the same folder as the workbook whenever the workbook is closed
- message box added to provide user with option if workbook is opened and closed several times in a day
- amend to suit your own requirements

Place code in ThisWorkbook module (it does NOT work in a standard module)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call BackupCopy
End Sub

Private Sub BackupCopy()
    If MsgBox("Create a backup?", vbYesNo) <> vbYes Then Exit Sub
    Dim Nm As String, Extn As String, fNm
    With ThisWorkbook
        fNm = .FullName
        Nm = Mid(fNm, 1, InStrRev(fNm, ".") - 1)
        Extn = Replace(fNm, Nm, "")
    End With
    Application.DisplayAlerts = False
    ThisWorkbook.SaveCopyAs Nm & " backup " & Format(Date, "YYMMDD") & Extn
    Application.DisplayAlerts = True
End Sub

 

[Hyp40]

This is brilliant. Thank you so much, it works perfectly. Ideally I would like the user name too but I can live without that.

 

[Hyp40]

Thank you. This is super. Perfect.

 

[Yongle]

Ideally I would like the user name too

Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("A:Y")) Is Nothing Then Range("Z" & Target.Row).Resize(, 2) = Array(Now, Application.UserName)
End Sub

which places Excel user name in the cell
To use the Windows user name replace Application.UserName with Environ("UserName")

 

[Hyp40]

Thank you Yongle, you have been amazing.