Automatically Save an Excel spreadsheet

chelly · Mar 17th 2020

HI,

I have a workbook that is in use 24/7 and as much as I drum it into people they do not save the spreadsheet on a regular basis. Every time an entry is input they should save the spreadsheet.

Ive had a look around and im struggling to come up with some VBA code to action this automatically.

Can anyone help?

Mumps · Mar 17th 2020

What do you want to trigger the macro to save the workbook?

ArdusPetus · Mar 17th 2020

Paste this in your worksheet code,

then save the workbook as xlsm.

Code

Option Explicit


Dim Counter As Long
Const MAXCHANGES As Long = 10


Private Sub Worksheet_Change(ByVal Target As Range)
Counter = Counter + 1
If Counter > MAXCHANGES Then
    Application.StatusBar = "Saving..."
    Me.Parent.Save
    Counter = 0
    Application.StatusBar = "Done!"
End If
End Sub

Display More

chelly · Mar 17th 2020

I have tried the code above but it is causing errors and stopped my previous code from working?

Code

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 1 Then
 Application.EnableEvents = False
 If Not IsEmpty(Target) Then
 Cells(Target.Row, 2).Value = Date + Time
 Else
 Cells(Target.Row, 2).ClearContents
 End If
 ElseIf Target.Column = 4 Then
 Application.EnableEvents = False
 If Not IsEmpty(Target) Then
 Cells(Target.Row, 5).Value = Date + Time
 Else
 Cells(Target.Row, 5).ClearContents
 End If
 End If
 Application.EnableEvents = True
End Sub

Option Explicit
Dim Counter As Long
Const MAXCHANGES As Long = 10
Private Sub Worksheet_Change(ByVal Target As Range)
Counter = Counter + 1
If Counter > MAXCHANGES Then
 Application.StatusBar = "Saving..."
 Me.Parent.Save
 Counter = 0
 Application.StatusBar = "Done!"
End If
End Sub

Display More

Quote from ArdusPetus

Paste this in your worksheet code,

then save the workbook as xlsm.

Code

Option Explicit


Dim Counter As Long
Const MAXCHANGES As Long = 10


Private Sub Worksheet_Change(ByVal Target As Range)
Counter = Counter + 1
If Counter > MAXCHANGES Then
    Application.StatusBar = "Saving..."
    Me.Parent.Save
    Counter = 0
    Application.StatusBar = "Done!"
End If
End Sub

Display More

chelly · Mar 17th 2020

Quote from Mumps

What do you want to trigger the macro to save the workbook?

Below is the table C to G is updated for a new entry of issing a key. A & B is updated when a key is returned.

I need this to save either automatically when updated or on a regular basis

Returned To	Time returned	Person issuing keys	Key number	Date & Time Issued	Name	Department
B BABBS

**royUK** · Mar 18th 2020

If you have Office 365 and share the workbook there(which is the best way) then you can use AutoSave

chelly · Mar 19th 2020

Quote from royUK

If you have Office 365 and share the workbook there(which is the best way) then you can use AutoSave

I have received the below error unable to share the work book. There is only one user ID that will be using the spreadsheet.

**royUK** · Mar 19th 2020

You don't need to share it if only one person is using it. The feature you need in Office 365 is Collaborate

Collaborate from anywhere using Office 365

Mumps · Mar 19th 2020

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Save the workbook as a macro-enabled file. This macro will be triggered automatically after a Return Time has been entered in column B and after a Department has been entered in column G so it is assumed that these two columns will always be populated.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B,G:G")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

**Jack in the UK** · Mar 20th 2020

These sorts of codes are great as backups, were all guilty on not saving enough

This is save the active workbook, assumes already named and saved, and really only replaces using CTRL+S keys pressed together on the keyboard

Admitted there are in this code as is common parameters to that save, thou again that exposes rick

Learn to use CTRL+S always and as you work, no matter what safety measures you have in place

Just a point, nothing more

jiuk

Automatically Save an Excel spreadsheet

Participate now!

Share