Need Help! Create New folder and save file in the new folder

[Caveman1964]

Hi All!
New to VBA. Struggling with this. Would appreciate any help
I want to,
open a workbook
save it in a new directory from created from cell h5 value
F:\Job Packets"H5"\my new file name (which will be value from h5 as well followed by the word "lot"
I have macro to save work book as the cell plus the lot and a separate to make new folder but cannot figure out how to have it make new folder and save new filename in the new folder.
Embarrassed to post this macro, yes I am aware this is crap please rewrite whole thing if needed. I need a chdir in there somewhere.
Thank You ahead of time.
I'll send you a six pack or something.
Thanks Caveman

Sub CreateFolderAndCopy()
Dim fileName As String
With Sheets(1)
If (.Range("H5") = vbNullString) Then Exit Sub
On Error Resume Next
MkDir "F:\Job Packets" & .Range("H5")
On Error GoTo 0
Dim NewFN As Variant
NewFN = "F:\Job Packets\ " & Range("H5").Value & ("LotMaterial") & ".xlsm"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close
End With
End Sub

 

[Fluff]

Hi & welcome to the board
Is this what you want?

Sub CreateFolderAndCopy()
   Dim fileName As String
   With Sheets(1)
   If .Range("H5").Value = vbNullString Then Exit Sub
   On Error Resume Next
   MkDir "F:\Job Packets" & .Range("H5").Value
   On Error GoTo 0
   Dim NewFN As Variant
   NewFN = "F:\Job Packets" & .Range("H5").Value & "\" & .Range("H5").Value & "(LotMaterial)" & ".xlsm"
   ActiveWorkbook.SaveAs NewFN, FileFormat:=52
   ActiveWorkbook.Close
   End With
End Sub

 

[Caveman1964]

First of all...Thank you very much!
Its almost doing what I needed. And, if I had to I would live with it.
Looking at my original post, I am not clear and for you to decipher that, was a challenge and I appreciate.
It is creating the folder under "F:\Job Packets\ 123456" ...(Perfect).....and the 123456.xlsm file in the "job packets" folder. Only one thing missing is I wanted it to put the file in the newly created folder like this "F:\Job packets\123456\ 123456.xlsm. Whereas the actual file is under "Job Packets" F:\Job Packets\123456.xlsm , instead of the newly created folder of 123456.
I hope this makes sense.....its almost there.
I want to end up with "F:\Job Packets\123456\123456.xlsm" .....the 123456 value is coming from H5
Thank You

 

[Fluff]

How about

Sub CreateFolderAndCopy()
   Dim fileName As String
   With Sheets(1)
   If .Range("H5").Value = vbNullString Then Exit Sub
   On Error Resume Next
   MkDir "F:\Job Packets\" & .Range("H5").Value
   On Error GoTo 0
   Dim NewFN As Variant
   NewFN = "F:\Job Packets\" & .Range("H5").Value & "\" & .Range("H5").Value & "(LotMaterial)" & ".xlsm"
   ActiveWorkbook.SaveAs NewFN, FileFormat:=52
   ActiveWorkbook.Close
   End With
End Sub

 

[Caveman1964]

Wait!.....I ran it again, not sure what I did.
It create F:\Job Packets123456\123456.xlsm
I am still looking at it and trying to learn so don't want you to waste too much time.
Not sure what happened the other time. I may have ran another macro.
Thanks

 

[Caveman1964]

Wow!......Thank you so much. yes that is perfect.
I want to send you a gift card. What do you like? Food, coffee? What is close to you?
I'll send you my email in a message if thats possible.
Thank you for spending the time.

 

[Fluff]

Glad to help & thanks for the feedback

I want to send you a gift card.

That's very kind of you, but letting me know it works & a word of thanks is all I need.
(Also offering payment is against the rules)

 

[Caveman1964]

Ah ok.....I see. Well you are a blessing.
Wishing you many blessings.
Peace