Saving a workbook in two places.

L

Luke Slotwinski

I am seeking to save a workbook in two places, having the second save
automatic.
In detail: I'd like to be able to specify the save location of the Save
request, but I'd like to automatically save a copy (or a backup) in another
folder on another drive... is this possible?
 
B

Bob Phillips

Look at SaveCopyAs in VBA help.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
G

Gord Dibben

Luke

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


Gord Dibben MS Excel MVP
 
J

jstro01

Gord

I see that this worked for the other user, however I am not familiar with
VB. So if you could give me some more direction on where this code should go.

Thanks
 
G

Gord Dibben

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord
 
J

jstro01

Thanks, this is the only line of code I can't get to work correctly.

ActiveWorkbook.SaveCopyAs FileName:="C:\My Documents\Doug Berke" & ???

I'm not sure what goes where the question marks are.
 
G

Gord Dibben

ActiveWorkbook.SaveCopyAs FileName:="C:\My Documents\Doug Berke\" _
& ActiveWorkbook.Name

Will save a copy with the same name to the Doug Berke folder.

Do you want to specify a name to save as?

Sub BUandSave2()
'Saves the current file to a backup folder and the default folder
'Note that any backup is overwritten if myname is duplicated
Dim myname As String
myname = InputBox("enter a name")
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:="C:\My Documents\Doug Berke\" & _
myname & ".xls"
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub


Gord
 
Top