Saving in Multiple Locations

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.

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

HI ITS CODE ARE WHICH FILE FORMAT IN SAVE

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

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

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

Why is that?

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

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

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.

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.

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?