Save File to Folder Based on Cell Value

[BluHound]

Hi Folks,

So I have two Subs that I would like to work together. One to create a new folder, if it doesn't already exist and the second to SaveAs with the file name based on cell values.

I would like for the code to

1) create the folder (if needed)
2) SaveAs with new file name (based on cell values)
3) Place new file in the folder created.

So far I have items 1 and 2 working independently, but the moment have yet to be able to get the workbook to save to the folder created with Sub IfNewFolder.

If anyone can help, i'd greatly appreciate it. I've been scouring the internet for almost a month and have now been given a dead line (if i can't get it to work, we'll just go back to the old way. doable, but deplorable )

Sub IfNewFolder()

Dim part1 As String
Dim part3 As String
Dim FolderCreate As String

part1 = Range("E4").Value
part3 = Range("C10").Value

If Len(Dir("O:\Human Capital\Training\Client Facing Training\Customers\ " & part3, vbDirectory)) = 0 Then
   MkDir "O:\Human Capital\Training\Client Facing Training\Customers" & part3
End If

End Sub

Sub SaveCustomizedCourse()
     
Dim part1 As String
Dim part3 As String


part1 = Range("E4").Value 'Quote Number
part3 = Range("C10").Value 'Company Name


ChDir "O:\Human Capital\Training\Client Facing Training\Customers\" ' From what I've read on the internet, this is telling excel to save files to this directory... 

' Creates file to directory Customers. But I can't get it to recognize the new folder created in the sub above...

ActiveWorkbook.SaveAs Filename:= _
"O:\Human Capital\Training\Client Facing Training\Customers" & part1 & "_" & part3 & "_Custom.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


End Sub

Thank you,

Blu

 

[ask2tsp]

Please try the following code on a copy of your workbook.
Comments on changes are in the code

Option Explicit
'----- by declaring the base path here _
       it's easier to change and _
       code is easier to read and less prone to typos _
 -----
Const MYPATH As String = "O:\Human Capital\Training\Client Facing Training\Customers\"

Sub IfNewFolder()

'Dim part1 As String 'this variable is not used -----
Dim part3 As String
'----- Dim FolderCreate As String  'this variable is not used -----

'----- part1 = Range("E4").Value 'not used here -----
part3 = Range("C10").Value

If Len(Dir(MYPATH & part3, vbDirectory)) = 0 Then
   MkDir MYPATH & part3
End If

End Sub
Sub SaveCustomizedCourse()
     
Dim part1 As String
Dim part3 As String

part1 = Range("E4").Value 'Quote Number
part3 = Range("C10").Value 'Company Name

IfNewFolder 'create company subfolder

'ChDir MYPATH ' From what I've read on the internet, this is telling excel to save files to this directory...
'-----you don't have to do that because this path is included in the filename in the SaveAs below -----

' Creates file to directory Customers. But I can't get it to recognize the new folder created in the sub above...

'ActiveWorkbook.SaveAs Filename:= _
 MYPATH & part1 & "_" & part3 & "_Custom.xlsm", FileFormat:= _
 xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

'----- instead put company in path (point to company subfolder) -----
ActiveWorkbook.SaveAs Filename:= _
MYPATH & part3 & "\" & part1 & "_Custom.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End Sub

 

[BluHound]

@ask2tsp Thank you so much! This works great. Though I'm not exactly sure how it functions (still learning), it does work and i've been having great results. Thank you for taking the time to help me with this.