Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Saving in Multiple Locations.

Saving in Multiple Locations

by Allen Wyatt
(last updated July 13, 2017)

11

You may have a need to routinely copy a workbook to multiple locations on your system. For instance, the open workbook may need to be copied to a local hard drive and to several mapped drives that are actually on your office network.

Excel doesn't have a built-in capability to do this, but if the various locations are well defined, you can create a macro that will do the saving for you. The following macro is an example of such a tool:

Sub SaveToLocations()
    Dim OrigName As String

    OrigName = ActiveWorkbook.FullName
    ActiveWorkbook.SaveAs "G:\" + ActiveWorkbook.Name
    ActiveWorkbook.SaveAs "L:\" + ActiveWorkbook.Name
    ActiveWorkbook.SaveAs "K:\" + ActiveWorkbook.Name
    ActiveWorkbook.SaveAs "S:\" + ActiveWorkbook.Name
    ActiveWorkbook.SaveAs OrigName
End Sub

The particular example of the macro saves the active workbook to five different locations, all using the same workbook name. The macro determines the current location of the workbook so that it can save to the current location last. The reason this is done is so that you can continue to use the regular Save tool and get the expected results.

If you want to use this macro on your own system, all you need to do is to make sure that you change the drive letters of where each workbook will be saved. If one of the drives you specify is for a location that uses removable media, and there is no media in the drive, then the macro will generate an error and stop. You'll then have to figure out where the workbook was originally saved so you can manually resave it there (using Save As).

Another peculiarity of the macro is that since it uses the SaveAs method, if there is already a workbook at each of the destinations with the same name as the current workbook, Excel will ask if you want the existing version of the workbook overwritten. This will always be the case with the last save, into the original location.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12495) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Saving in Multiple Locations.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Adding a File Path and Filename

If you need to stuff the current workbook's filename and path into a cell or a header or footer, you'll appreciate the ...

Discover More

Displaying the Document Map

One of the viewing modes you can use for a document involves the use of the Document Map. This shows a quick outline of your ...

Discover More

Embedding an Excel Worksheet

Word and Excel are both integral parts of Microsoft's Office suite of applications. As such, Word allows you to embed ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Saving a Workbook with a Preview

When you save your workbooks, Excel can also save a preview image (thumbnail) that can be displayed in the Open dialog box. ...

Discover More

File Formats that Include Field Formats

If you import data into Excel that is created by other programs, you know that it can be bothersome to get your data ...

Discover More

Creating a Dated Backup File

As you are developing your workbooks, you might want a way to automatically create backup files that include a date and time ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two more than 4?

2016-01-22 07:42:33

swatsp0p

For wallyboyes:

Entering macros is not real difficult, however the steps are more than can be given here. I recommend you Google "how to enter macros in Excel" to find step by step instructions. I'm 65 (just a baby), but I figured it out with some help like this.


2016-01-21 08:33:19

wallyboyes

I am a pensioner learning how to use Excel I keep a spreadsheet of credits and debits and for safety I save it to my hard drive and some usb drives.

The article explained how to help but did not explain how to accomplish it.

Can you point me in the direction to learn how.
In passing I am a 79 year old so simple please

regards

Wally


2015-07-15 13:34:10

KRUTAL KEVADIYA

HI ITS CODE ARE WHICH FILE FORMAT IN SAVE


2013-10-11 15:56:18

Aldo

There is suppose to be a backslash between Desktop and SFB in "DesktopSFB". Not sure why it didn't show in post.


2013-10-11 15:51:53

Aldo

I wanted to do multiple saves at various locations (not in a root directory though). I use various cloud drives and the local HDD for this. I have several devices and systems each with a different Workbook Path and user directory location. But the main folders are named the same way on each system.

I found using the ChDir function was the key to make this work properly. The 'Save as' in Excel uses the current directory path as the default location, so by changing the default location before saving eliminated unusual errors I was getting before.

...

Sub SaveAs_AllLocations()
Dim Location As String

'Start with an Origin Location, in this case my desktop location
Location = ActiveWorkbook.Path
If InStr(1, ActiveWorkbook.FullName, "DropBox", vbTextCompare) > 0 Then Location = Replace(Location, "DropBox", "DesktopSFB", , , vbTextCompare)
If InStr(1, ActiveWorkbook.FullName, "SkyDrive", vbTextCompare) > 0 Then Location = Replace(Location, "SkyDrive", "DesktopSFB", , , vbTextCompare)
If InStr(1, ActiveWorkbook.FullName, "Google Drive", vbTextCompare) > 0 Then Location = Replace(Location, "Google Drive", "DesktopSFB", , , vbTextCompare)
If InStr(1, ActiveWorkbook.FullName, "My Cubby", vbTextCompare) > 0 Then Location = Replace(Location, "My Cubby", "DesktopSFB", , , vbTextCompare)

'DropBox
Location = Replace(Location, "DesktopSFB", "DropBox")
ChDir Location
ActiveWorkbook.SaveAs Filename:=Location & Application.PathSeparator & ActiveWorkbook.Name, FileFormat:=xlExcel12, ConflictResolution:=xlLocalSessionChanges

'SkyDrive
Location = Replace(Location, "DropBox", "SkyDrive")
ChDir Location
ActiveWorkbook.SaveAs Filename:=Location & Application.PathSeparator & ActiveWorkbook.Name, FileFormat:=xlExcel12, ConflictResolution:=xlLocalSessionChanges

'Google Drive
Location = Replace(Location, "SkyDrive", "Google Drive")
ChDir Location
ActiveWorkbook.SaveAs Filename:=Location & Application.PathSeparator & ActiveWorkbook.Name, FileFormat:=xlExcel12, ConflictResolution:=xlLocalSessionChanges

'My Cubby
Location = Replace(Location, "Google Drive", "My Cubby")
ChDir Location
ActiveWorkbook.SaveAs Filename:=Location & Application.PathSeparator & ActiveWorkbook.Name, FileFormat:=xlExcel12, ConflictResolution:=xlLocalSessionChanges

'Desktop
Location = Replace(Location, "My Cubby", "DesktopSFB")
ChDir Location
ActiveWorkbook.SaveAs Filename:=Location & Application.PathSeparator & ActiveWorkbook.Name, FileFormat:=xlExcel12, ConflictResolution:=xlLocalSessionChanges

End Sub


2013-10-08 14:17:29

Kendell

This code seems to work fine....the first time. It never works again when you push Save.

Why is that?


2013-03-05 11:19:32

Don

Because we support several workbooks that need to be distributed and backed-up, we have a worksheet in shared workbook that contains the "rootname" of the files (e.g. "ABCDEF 2013-03-03.xlsx" would have a "rootname" of "ABCDEF")in column A and the target directory in Column B.

Sub DistFiles()

Dim i As Double
Do Until Len(ThisWorkbook.Worksheets("SaveControls").Cells(i, 1)) = 0
If InStr(1, ThisWorkbook.Worksheets("SaveControls").Cells(i, 1), ActiveWorkbook.Name) > 0 Then _
ActiveWorkbook.SaveCopyAs = _
ThisWorkbook.Worksheets("SaveControls").Cells(i, 2) & ActiveWorkkbook.Filename
Next i

End Sub


2013-03-04 08:59:35

AG

So how would this work in a Mac-only network environment?


2013-03-02 13:04:19

swatsp0p

CORRECTION:

for some reason the backslash character does not show in my original posting of code.
ActiveWorkbook.SaveCopyAs "D:-MyFolder-FileName bu1.xlsx"
ActiveWorkbook.SaveCopyAs "H:-Backups--MyFolder-FileName bu2.xlsx"
ActiveWorkbook.SaveCopyAs "D:-MyFolder-FileName bu " & MyStr & ".xlsx"

I hope you can assume the proper format for entering these file locations.


2013-03-02 12:59:47

swatsp0p

I use a macro tied to a button in my sheet to save and make two backups in differing locations with a simple button click. I also use a method to make sequential backups with the time stamp in the file name:

Private Sub CommandButton1_Click()
' SAVE AND BACKUP FILE
Dim myDate As Date
Dim MyStr As String

myDate = Now
MyStr = Format(myDate, "yyyy mm dd hh-mm")

ActiveWorkbook.SaveCopyAs "D:MyFolderFileName bu1.xlsx"
ActiveWorkbook.SaveCopyAs "H:BackupsMyFolderFileName bu2.xlsx"
ActiveWorkbook.SaveCopyAs "D:MyFolderFileName bu " & MyStr & ".xlsx"

ActiveWorkbook.Save

End Sub

...where FileName is the actual name of the file being saved. This is a single use macro (only for this particular file).

The backup with the timestamp makes it easy to go back and find a point in time if I need to correct errors in a subsequent file.


2013-03-02 08:13:57

MJCarman01

In your code example, does the '+' character function directly as a concatenation character, or does it have some other purpose? If concatenation, why not use the more familiar '&' character?


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.