Save to Folder Name based on cell value

[kclong]

In Excel 2007, how do I write a macro that will save to a specific folder based on a cell's value? For example, if I have folders Red, Blue, and White, and a cell where I input Blue, I want the macro to then save the file in the Blue folder. If I change the cell value to Red, the macro will then save to the Red folder, etc. Thanks

 

[JoeMo]

Try this (change the range where folder name resides to suit):

Sub SaveToFolder()
Dim fPath As String, mPath As String, foldName As Variant
Dim filName As String, fullPath As String
'your code here
'Ready to save
filName = ThisWorkbook.Name
fPath = ThisWorkbook.Path
mPath = Left(fPath, InStrRev(fPath, "\") - 1)
foldName = Range("A1").Value   'Change range to suit
fullPath = mPath & Application.PathSeparator & foldName & Application.PathSeparator & filName
ThisWorkbook.SaveAs Filename:=fullPath
End Sub

 

[kclong]

Thanks, but I can't seem to get it to work, but that's because I don't fully understand the code. To be more specific about my path, if I want to to go to u:\ken\excel\, and at times I may change it to a different path, such as u:\ken\projects\, how would I change the code? Thanks again for your help.

 

[JoeMo]

Can you be more specific about what's not working?
The code is designed to save the workbook it resides in to a pre-existing folder whose name is in Range("A1") of the sheet that's active when the code is run. The pre-existing folder is on the same path as the workbook was on when it was opened, except for the folder if the Range("A1") value was changed after opening and prior to running the code.

If you are starting with a new workbook and/or there is no pre-existing folder, this code will not work.

 

[kclong]

The file is currently in u:\ken\excel\. I want to change from the "excel" directory to "other" directory. When I put other in A1 and run the macro, it comes back with:

Microsoft Office Excel cannot access the file 'C:\Users\Ken\Appdata\Roaming\Microsoft\Excel\other\CED44000'.

Any thoughts?

 

[JoeMo]

The code I posted would place the file in u:\ken\excel\other if a folder named 'other' exists in that directory.

 

[kclong]

The directory u:\ken\excel\other exists, but the message says it wants to save it to:

'C:\Users\Ken\Appdata\Roaming\Microsoft\Excel\other\CED44000'

Why is it putting CED44000 at the end? Could that be the problem?

 

[kclong]

Actually the problem seems to be that the code is changing u:\ken\excel\ to C:\Users\Ken\Appdata\Roaming\Microsoft\Excel\. Any idea how to change that?

 

[JoeMo]

In any empty cell in the workbook you are running the code from enter this formula:

=CELL("filename",A1)

and post what the cell returns.

 

[kclong]

U:\Ken\Excel\[Test.xlsx]Sheet1