VBA code to Save file to specific Folder based on Cell input

[Kingkoopa]

Hi Guys,

I am using Excel 2016. I have the following input fields :

[TABLE="width: 251"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Sales Person[/TD]
[TD]Jack[/TD]
[/TR]
[TR]
[TD]Customer[/TD]
[TD]Apple Inc[/TD]
[/TR]
[TR]
[TD]Project[/TD]
[TD]Glass[/TD]
[/TR]
</tbody>[/TABLE]

I am trying to setup a macro that will save my excel file to a specific folder name base on the sales person name. So if the sale's person name is Jack it would go to Jack folder. If its John it will save to John folder etc.

I did abit of googling and found the code to save to a folder but wasn't able to find one that had a dynamic save location :

Sub TestSave()

Dim FName As String
Dim Fname2 As String
Dim Fname3 As String
Dim FPath As String


FPath = "C:\Desktop\Jack/"
FName = Range("B1")
Fname2 = Range("B2")
Fname3 = Range("B3")
ActiveWorkbook.SaveAs Filename:=FPath & FName & " " & Fname2 & Fname3 & ".xls", FileFormat:=xlNormal

End Sub


Any idea's how I can make it dynamic such that it would save to the specific folder base on the cell input of Cell B1 ?


Thank you

 

[Joe4]

You told us the file folder you want it saved to, but not what you want to name the file.
Where does the file name come from?

 

[Kingkoopa]

Hi Joe,

The file name comes from the cell B1 , B2 & B3.

[TABLE="class: cms_table, width: 251"]
<tbody>[TR]
[TD]Sales Person[/TD]
[TD]Jack[/TD]
[/TR]
[TR]
[TD]Customer[/TD]
[TD]Apple Inc[/TD]
[/TR]
[TR]
[TD]Project[/TD]
[TD]Glass[/TD]
[/TR]
</tbody>[/TABLE]
So the name of the file would be Jack Apple Inc Glass. If B1 is Jack it would save to Jack folder, If its John it would save to John folder etc. (Can't figure out this portion ).

Thank you

 

[Joe4]

Try:

[COLOR=#333333]Sub TestSave()[/COLOR]

[COLOR=#333333]Dim FName As String[/COLOR]
[COLOR=#333333]Dim Fname2 As String[/COLOR]
[COLOR=#333333]Dim Fname3 As String[/COLOR]
[COLOR=#333333]Dim FPath As String[/COLOR]

[COLOR=#333333]FName = Range("B1")[/COLOR]
[COLOR=#333333]Fname2 = Range("B2")[/COLOR]
[COLOR=#333333]Fname3 = Range("B3")[/COLOR]
[COLOR=#ff0000]FPath = "C:\Desktop\" & Fname & "\"[/COLOR][COLOR=#333333]
[/COLOR][COLOR=#333333]
ActiveWorkbook.SaveAs Filename:=FPath[/COLOR][COLOR=#ff0000] [/COLOR][COLOR=#333333]& Fname & " " & Fname2 & " " & Fname3 & ".xls", FileFormat:=xlNormal[/COLOR]

[COLOR=#333333]End Sub[/COLOR]

Note the changes to the FPath calculation and the placement of it (needs to be AFTER Fname is set).

 

[Kingkoopa]

It worked thanks alot! :D