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

Hyp40

New Member
Joined
Apr 9, 2019
Messages
16
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,706
Office Version
365
Platform
Windows
Code:
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

Code:
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
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,706
Office Version
365
Platform
Windows
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)
Code:
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
 
Last edited:

Hyp40

New Member
Joined
Apr 9, 2019
Messages
16
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

New Member
Joined
Apr 9, 2019
Messages
16
Thank you. This is super. Perfect.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,706
Office Version
365
Platform
Windows
Ideally I would like the user name too
Code:
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")
 
Last edited:

Forum statistics

Threads
1,082,358
Messages
5,364,914
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top