Auto Save

[Michael McGarrity]

Hi, I'm really new to VBA or complex Macros. I am looking for a macro
that would run automatically daily at 2am, save as
CorporateActionSummary MM.DD.YYY.xlsx (current date). Essentially I
would like a new worksheet saved and ready for me when I get in at 6am
with all of yesterdays data, I do not want to overwrite the original
sheet.

 

[Vacuum Sealed]

Hi Michael

I use something similar for backing up a file "Every Hour"

I modified the code (Not tested) to what I hope will do a run once save,
then stop.

I'm fairly certain some of the more seasoned Guru's will correct any
mistakes I may have made.

Lines below that preceed with ( ' ) are purely for comment and instruction
and not part of the actual command code

HTH...

' Copy / Paste this at the top of the Module window

Option Explicit
Public RunTime

' Then Copy / Paste this just under

Sub StartTimer()

RunTime = #2:00:00 AM#

Application.OnTime RunTime, "BackFileUp", schedule:=True

End Sub

Sub StopTimer()

On Error Resume Next
Application.OnTime RunTime, "BackFileUp", schedule:=False

End Sub

Sub BackFileUp()

Dim BackupFilePath As String
Dim BackupFileExtStr As String
Dim BackupFileName As String
Dim myWB As Workbook

With Application
.ScreenUpdating = False
End With

myWB = ActiveWorkbook

BackupFilePath = "T:\YourNetwork\YourDirectory"
BackupFileName = myWB.Name & " - " & Format(Date, "mm-dd-yyyy")
BackupFileExtStr = ".xlsx": FileFormatNum = 51

StartTimer

With myWB
.SaveAs BackupFilePath & BackupFileName & BackupFileExtStr,
FileFormat:=FileFormatNum
End With

StopTimer

With Application
.ScreenUpdating = True
End With

End Sub

' In the Left Window Pane of the VB Editor you will see the Workbook Name in
bold
' Double Left-Click it
' In the Right Window Pane Copy / Paste these 2 Private Sub Routines

Private Sub Workbook_Open()
StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopTimer
End Sub

Good luck

Regards
Mick

 

[Vacuum Sealed]

Just a follow up Michael

This code only runs if / when the workbook is actually opened.

Also, here's some Websites that have some great codes which you may find
handy....

Ron DeBruin: http://www.rondebruin.nl/tips.htm
Chip Pearson: http://www.cpearson.com/Excel/Topic.aspx
Debra Dalgleish: http://www.contextures.com/tiptech.html

HTH
Mick