I do something like that with my Personal.xls workbook. Not to share with
others, just for peace of mind in case my laptop gets nicked. It means I
don't have to remember to save it once never mind twice. It also means
writing code, and since you are in the General Questions section, I don't
know how familiar you are with that, but here goes. I've modified my code
slightly but it should work for you if you change the constants to point at
drive paths relevant to you.
In the code page for the 'ThisWorkbook' object put:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Archive_PWB
End Sub
The rest can go in any module in the same workbook, but for simplicity you
could put it in the same place (but if you do, the constants must go to the
top of the sheet):
Const Localpath As String = "C:\mySharedFiles\"
Const Networkpath As String = "\\myNetworkDrive\myFolder\"
Public Sub Archive_PWB()
If Not ActiveWorkbook.Saved Then
'Save it locally (if new) or overwrite existing (if not) before ...
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Localpath & ActiveWorkbook.Name
Application.DisplayAlerts = True
'... doing the archive
Copy_to_Network_Drive
End If
Exit Sub
End Sub
Private Sub Copy_to_Network_Drive()
Dim Answer As String
Dim fsObject As Object
On Error GoTo eCode
Set fsObject = CreateObject("Scripting.FileSystemObject")
fsObject.copyfile _
Localpath & ActiveWorkbook.Name, _
Networkpath & ActiveWorkbook.Name, _
True 'overwrite
Set fsObject = Nothing
Exit Sub
eCode:
If Err.Number = 76 Then
'Network Drive unavailable.
Answer = InputBox("Network drive unavailable continue without
saving?" & vbCrLf & _
"If No, make it available before continuing.", _
"Copy_to_Network_Drive", "Y")
If UCase(Answer) = "Y" Then
Resume Next
Else
Resume
End If
Else
MsgBox "Unexpected error: " & Err.Number & " - " & Err.Description,
, _
"Copy_to_Network_Drive"
End If
End Sub
