Make New Directory Workbook VBA

[Threshold]

Hi All,

So I have this code:

Sub SaveFilewithNewName()    Dim NewFN As Variant
    Dim NewPth As String
    Dim WRK2 As Workbook
    PostToRegister
    'Copy File to New Path and Filename
    ActiveSheet.Copy
    'Change File Path to Server File Path when ready
    NewPth = "E:\NewProject\File\" & Range("K3")
    MkDir NewPth
    NewFN = NewPth & "\" & Range("K3").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextFile
End Sub

Currently I get the error at MkDir NewPth, NewPth doesn't seem to add the K3 Range... even though when I hover over the name it shows the value for the Range, but doesn't show it as part of NewPth.

Thanks in advance

 

[Threshold]

It's blank now
At one point it was giving me the 26 I needed. Not the case any longer.

 

[Threshold]

(repeat message, please ignore)

 

[Threshold]

ok, I figured out a little more. It's copying the wrong sheet. It's copying the the file that it's opening. I want it to copy the file that was previously open.
I think that's why I had in the bottom code

DIM WRK2 as Workbook

But I'm not sure how I want to keep it that way...

 

[Fluff]

Try this

Sub SaveProjectswithNewName()
    Dim NewFN As Variant
    Dim NewPth As String
    Dim Ws As Worksheet
    Set Ws = ActiveWorkbook.Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
    
    PostToRegister
    'Copy Projects to New Path and Filename
    Ws.Copy
    'Change File Path to Server File Path when ready
    NewPth = "E:\Projects\File\" & Ws.Range("K3").Value
    MsgBox NewPth
    MkDir NewPth
    NewFN = NewPth & "\" & Ws.Range("K3").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextFile
End Sub

Change the sheet name in red to suit

 

[Threshold]

Ok I figured it out.
Where it says

Sub SaveECOwithNewName()    Dim NewFN As Variant
    Dim NewPth As String
    Dim WRK2 As Workbook
    PostToRegister
    'Copy ECO to New Path and Filename
[COLOR=#ff0000]    [/COLOR][COLOR=#008000]ThisWorkbook[/COLOR][COLOR=#ff0000].ActiveSheet.Copy[/COLOR]
    'Change File Path to Server File Path when ready
    NewPth = ("E:\Phil - UKM Projects\ECO\" & Range("K3"))
    MkDir NewPth
    NewFN = NewPth & "\" & Range("K3").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextECO
End Sub

I added the green part. This works for me. I know have a new issue. After it does all that. I want to save the other workbook and close it before it goes to the next part of the code "NextFile" (So the E:\Projects\File\File.XLSM)

 

[Fluff]

Try adding this

   WBK.Close True

to the end of the PostToregister code

 

[Threshold]

Thank you so much for the help!

 

[Fluff]

Your welcome