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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

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,123,351
Messages
5,601,130
Members
414,429
Latest member
Bilaal xaka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top