VBA save in folder and subfolder based on cell value

jimmyjimmyv

New Member
Joined
Jun 20, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi, I've looked around here for the answer but could find any like mine and my go-to IT Mgr. no longer works here, so I'm having to learn all of this on my own. The VBA code I have saves the current .xlsm as .xlsx as it's supposed to. It is a checklist and I'm wanting to save the workbook in a known folder S:\myfiles, in a subfolder named as "customer" in K1, then subfolder "customer part number" named in K2.

So, S:\myfiles\K1 value\K2 value\Customer Name - Customer Part number.xlsx

If the first subfolder isn't made, create it. If it's already made, just create the second subfolder, it will always need to be created.

Here's what I have that works well at saving in the main folder.

Sub FileNameAsCellContent()
Dim FileName As String
Dim Path As String
Application.DisplayAlerts = False
Path = "S:\myfiles\"
FileName = Range("K1").Value & ".xlsx"
ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbook
Application.DisplayAlerts = True
End Sub

I tried adding the following, but it didn't work.
Path = "S:\myfiles\" & [K2] & "\" & [K3] & "\"
MakeDirMulti (Path)

I'm on 365, if that helps any. Thanks a WHOLE BUNCH for any help you can give me.
Jimmy
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Forum statistics

Threads
1,214,638
Messages
6,120,674
Members
448,977
Latest member
moonlight6

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top