Automatically save a copy of worksheet from C: to Network Drive

D

doosbug

I have a spreadsheet that I work on my C: drive that I need to have a copy
automatically saved (undated everytime I make changes) on a network drive so
my boss can access it when she needs to review it. Can I do this?
 
G

Gord Dibben

doos

VBA macro run from a button or shortcut key combo...

Sub BUandSave2()
'Saves the current file to a backup folder and the default folder
'Note that any backup is overwritten
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs FileName:="E:\GordStuff\Backup\" & _
ActiveWorkbook.Name
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub

You could alternatively place this code in a BeforeSave routine in This
Workbook module.

Right-click on the Excel logo left of "File" on menu and select "View Code"
then paste into that module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs FileName:="E:\GordStuff\Backup\" & _
ActiveWorkbook.Name
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub

Edit the path in the code to your network path.


Gord Dibben Excel MVP
 
Top