I need a macro that saves a spreadsheet on two drive at once,

C

Chauncy_G

I need a way to save a spreadsheet on two different drives at once, (1 for
backup). Ideally this will be done with the minimum of moves, one button or
cloick would be nice.
 
B

Barb Reinhardt

I'm thinking you could put this in a Workbook_BeforeSave event. I've never
done it. You may want to ask in the Programming group.
 
T

tim m

In the past I've just used 'record macro' to manually save to each location.
Then i either assign the macro to a button or make a macro short cut and use
that.
 
G

Gord Dibben

Chauncy

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:="C:\Gordstuff\" & _
ActiveWorkbook.Name
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub

Adjust the C:\Gordsuff path.

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 the macro by going to Tool>Macro>Macros.

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

Or stick it in a BeforeSave event in Thisworkbook for no key strokes at all.

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



Gord Dibben MS Excel MVP

I need a way to save a spreadsheet on two different drives at once, (1 for
backup). Ideally this will be done with the minimum of moves, one button or
cloick would be nice.
Click to expand...

Gord Dibben MS Excel MVP
 
Top