Auto Save VBA

brian5857

Board Regular
Joined
Jan 28, 2005
Messages
64
I have the code below to auto save my worksheet named "Processors" every 30 minutes to the C: drive. Is there someway to have the code save the whole sheet as it does now, but save only the values in every cell and not the formulas?




In "This Woorkbook' I have this code:


Private Sub Workbook_Open()

'Disable events
Application.EnableEvents = False

Resp = MsgBox("Do you want to automaticaly backup Production Reports every 30 minutes?", _
vbQuestion + vbYesNo, "Auto Backup")

If Resp = vbYes Then
' START BACKUP TIMERS
Application.OnTime TimeValue("00:00:00"), "DoBackup"
Application.OnTime TimeValue("00:30:00"), "DoBackup"
Application.OnTime TimeValue("01:00:00"), "DoBackup"
Application.OnTime TimeValue("01:30:00"), "DoBackup"
Application.OnTime TimeValue("02:00:00"), "DoBackup"
Application.OnTime TimeValue("02:30:00"), "DoBackup"
Application.OnTime TimeValue("03:00:00"), "DoBackup"
Application.OnTime TimeValue("03:30:00"), "DoBackup"
Application.OnTime TimeValue("04:00:00"), "DoBackup"
Application.OnTime TimeValue("04:30:00"), "DoBackup"
Application.OnTime TimeValue("05:00:00"), "DoBackup"
Application.OnTime TimeValue("05:30:00"), "DoBackup"
Application.OnTime TimeValue("06:00:00"), "DoBackup"
Application.OnTime TimeValue("06:30:00"), "DoBackup"
Application.OnTime TimeValue("07:00:00"), "DoBackup"
Application.OnTime TimeValue("07:30:00"), "DoBackup"
Application.OnTime TimeValue("08:00:00"), "DoBackup"
Application.OnTime TimeValue("08:30:00"), "DoBackup"
Application.OnTime TimeValue("09:00:00"), "DoBackup"
Application.OnTime TimeValue("09:30:00"), "DoBackup"
Application.OnTime TimeValue("10:00:00"), "DoBackup"
Application.OnTime TimeValue("10:30:00"), "DoBackup"
Application.OnTime TimeValue("11:00:00"), "DoBackup"
Application.OnTime TimeValue("11:30:00"), "DoBackup"
Application.OnTime TimeValue("12:00:00"), "DoBackup"
Application.OnTime TimeValue("12:30:00"), "DoBackup"
Application.OnTime TimeValue("13:00:00"), "DoBackup"
Application.OnTime TimeValue("13:30:00"), "DoBackup"
Application.OnTime TimeValue("14:00:00"), "DoBackup"
Application.OnTime TimeValue("14:30:00"), "DoBackup"
Application.OnTime TimeValue("15:00:00"), "DoBackup"
Application.OnTime TimeValue("15:30:00"), "DoBackup"
Application.OnTime TimeValue("16:00:00"), "DoBackup"
Application.OnTime TimeValue("16:30:00"), "DoBackup"
Application.OnTime TimeValue("17:00:00"), "DoBackup"
Application.OnTime TimeValue("17:30:00"), "DoBackup"
Application.OnTime TimeValue("18:00:00"), "DoBackup"
Application.OnTime TimeValue("18:30:00"), "DoBackup"
Application.OnTime TimeValue("19:00:00"), "DoBackup"
Application.OnTime TimeValue("19:30:00"), "DoBackup"
Application.OnTime TimeValue("20:00:00"), "DoBackup"
Application.OnTime TimeValue("20:30:00"), "DoBackup"
Application.OnTime TimeValue("21:00:00"), "DoBackup"
Application.OnTime TimeValue("21:30:00"), "DoBackup"
Application.OnTime TimeValue("22:00:00"), "DoBackup"
Application.OnTime TimeValue("22:30:00"), "DoBackup"
Application.OnTime TimeValue("23:00:00"), "DoBackup"
Application.OnTime TimeValue("23:30:00"), "DoBackup"
End If

'Disable events
Application.EnableEvents = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Disable events
Application.EnableEvents = False

On Error GoTo TheEnd

' STOP BACKUP TIMERS
Application.OnTime EarliestTime:=TimeValue("00:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("00:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("01:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("01:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("02:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("02:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("03:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("03:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("04:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("04:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("05:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("05:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("06:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("06:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("07:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("07:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("08:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("08:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("09:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("09:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("10:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("10:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("11:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("11:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("12:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("12:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("13:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("13:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("14:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("14:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("15:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("15:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("16:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("16:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("17:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("17:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("18:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("18:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("19:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("19:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("20:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("20:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("21:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("21:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("22:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("22:30:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("23:00:00"), _
Procedure:="DoBackup", Schedule:=False
Application.OnTime EarliestTime:=TimeValue("23:30:00"), _
Procedure:="DoBackup", Schedule:=False

TheEnd:
'Disable events
Application.EnableEvents = True

End Sub





In A module have:


Private Sub DoBackup()
' DO BACKUP
Path = "C:\Documents and Settings\PDC Maintenance\Desktop\Production Reports\"
Fname = Format(Date, "MM-DD-YY") & Format(Time, " hhmm") & " hrs" & ".xls"

ThisWorkbook.Sheets("Reports").Copy
ActiveWorkbook.SaveAs Filename:=Path & Fname

ActiveWindow.Close
End Sub
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, Brian,
inserting some lines could solve your problem
at least it does for me
replace ThisWorkbook.Sheets("Reports").Copy by
Code:
Dim LR As Long, LC As Long, MyRange As Range
ThisWorkbook.Sheets("Reports").Copy
    With ActiveSheet
    LR = .Cells.Find("*", [A1], xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row
    LC = .Cells.Find("*", [A1], xlFormulas, xlPart, xlByColumns, xlPrevious, False, False).Column
    Set MyRange = .Range(Cells(1, 1), Cells(LR, LC))
    End With
MyRange.Value = MyRange.Value
the way you are scheduling the process is another question, you could run the save procedure each 30 minutes without needing all those different lines
kind regards,
Erik
 

Watch MrExcel Video

Forum statistics

Threads
1,118,812
Messages
5,574,455
Members
412,595
Latest member
slim313
Top