vba to save workbook to a subfolder

[Brian F]

Windows 8, Excel 2010; If I am working with any excel workbook and I run the command "ActiveWorkbook.Save", the workbook will be saved in the folder it came from. The workbook "knows" where it came from.

Is there a way to save workbook to a subfolder called "backup" that resides in the same parent folder as the original workbook? I don't want to describe the entire path because I want it to work on any computer so long as the parent folder has a subfolder called "backup". I want the routine to say "save it where you always do, except in the subfolder "backup".

 

[HalfAce]

Here's something you can try.
You'll need to create a sub folder in the parent folder your workbook gets opened from, named BackUp .

Sub SaveInBackupSubfolder()

Dim MyName As String, MyPath As String
MyName = ThisWorkbook.Name
MyPath = ThisWorkbook.Path

On Error Resume Next
ActiveWorkbook.SaveAs Filename:=MyPath & Application.PathSeparator & "BackUp" & Application.PathSeparator & MyName
If Err.Number = "1004" Then
  On Error GoTo 0
  GoTo JustSave
Else
  GoTo Quit
End If

JustSave:
ThisWorkbook.Save

Quit:
End Sub

It checks to see if the back up sub folder exists, and if so, does a SaveAs to it.
If it doesn't exist, it'll just save to the original folder it opened up from.
(This means you can do the save as, even if it was opened from the back up sub folder without any further monkeying around.)

Hope it helps.

 

[Brian F]

Works smoother than butter! Thanks so much. That will save me a lot of time and effort.

 

[Brian F]

When I incorporate your code into my routine I realize I made a mistake. After the file gets saved in "Backup" (which works perfectly) it needs to be resaved in the original folder (the "parent" folder). The idea is to leave an untouched version in a backup folder. My routine goes on and modifies the document in a dozen different ways and, as it is, that happens to the backup version and I need it to happen to the original. I'm sorry to impose on your generosity, but after saving it in the Backup could you have it resave in the original parent folder once again. (It would be resaving it over the original version, does that present an obstacle?)

 

[HalfAce]

Impose? Not at all. (We do this for fun!)
How about something like this?

Sub SaveInBackupSubfolder()

Dim MyName As String, MyPath As String
MyName = ThisWorkbook.Name
MyPath = ThisWorkbook.Path
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=MyPath & Application.PathSeparator & "BackUp" & Application.PathSeparator & MyName
If Err.Number = "1004" Then
  On Error GoTo 0
  MsgBox "The file path " & MyPath & Application.PathSeparator & "BackUp" & Application.PathSeparator & " can not be found." & vbNewLine & _
         "Please make sure this file path exists before saving."
  Application.DisplayAlerts = True
  Exit Sub
End If
ActiveWorkbook.SaveAs Filename:=MyPath & Application.PathSeparator & MyName
Application.DisplayAlerts = True
End Sub

Hope it helps.

 

[Brian F]

Once again, it works perfectly. You make it look easy. Thanks so much.

 

[HalfAce]

Thanks for the kind words. You're more than welcome.
Glad it helped.

 

[Pete2211]

Hi.
I realize that this question was answered quite some time ago but it is the closest I could find to the question I would like to ask.

I want to automatically save a workbook to a specific subfolder. The name of the subfolder is always in the format 00 Month, so 01 Jan,02 Feb,03 Mar etc.
I want an opened copy of a workbook named 'Corrections ddmmyyyy'.xlsx to be saved into the subfolder corresponding to the mm of the file.
I'm sure I can get it to save but how can I get it to select the correct subfolder? I assume that the subfolder could be chosen numerically, rather than the text

Many thanks in anticipation.