Auto Save VBA

B

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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Workbook with Timer Macro Saving After Closing
Replies
1
Views
426
John_w
J
Sean15
  • Solved
Help with code forcing specific workbook to close after inactivity.
Replies
2
Views
484
Sean15
Sean15
M
  • Question
My macro —which sets a timer— stops running when I open other windows.
Replies
0
Views
472
Miguelcb
M
S
  • Question
Running vba Application.OnTime TimeValue events in closed workbooks
Replies
1
Views
383
excel_learnerz
E
P
  • Question
Live Clock in a single cell
Replies
1
Views
524
DanteAmor
DanteAmor

Forum statistics

Threads
1,214,795
Messages
6,121,624
Members
449,041
Latest member
Postman24

Share this page

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
Back
Top