Macro/VBA Create new folder and save workbook as

[NVRensburg]

I've tried a couple examples from other posts but no luck yet. Please could someone help me with a code

I have a workbook called "Subcontractor Spreadsheet". I want to create a macro that would create a new folder in location "J:\Alaska New Filing System (In Progress)\Tenders\Tenders by Project Number" named on the contents of cell B2 and then save the workbook as (save a copy in other words) filename in cell B3 and B2 (so I don't want to affect my master document.)
Thanking you in advance!

 

[John_w]

Try this:

Public Sub Save_Workbook_Copy()

    Dim folder As String
    
    folder = "J:\Alaska New Filing System (In Progress)\Tenders\Tenders by Project Number\"
    
    CreateObject("WScript.Shell").Run "cmd /c mkdir " & Chr(34) & folder & Chr(34), vbMinimizedNoFocus, True
    
    With ThisWorkbook
        .SaveCopyAs folder & .ActiveSheet.Range("B3").Text & .ActiveSheet.Range("B2").Text & Mid(.FullName, InStrRev(.FullName, "."))
    End With
    
End Sub

 

[NVRensburg]

Thank you, this kind of works. It creates a copy of the spreadsheet in the folder "Tenders by Project Number" however it doesn't create a new folder in that folder. So I'm wanting it to create a new FOLDER in the folder "tenders by project number" and name that folder the text in cell B2 and then save that spreadsheet in that new folder.

 

[John_w]

Sorry, I missed the B2 folder requirement. Try this:

Public Sub Save_Workbook_Copy()

    Dim folder As String
    
    With ThisWorkbook
        folder = "J:\Alaska New Filing System (In Progress)\Tenders\Tenders by Project Number\" & .ActiveSheet.Range("B2").Text & "\"
        
        CreateObject("WScript.Shell").Run "cmd /c mkdir " & Chr(34) & folder & Chr(34), vbMinimizedNoFocus, True
    
        .SaveCopyAs folder & .ActiveSheet.Range("B3").Text & .ActiveSheet.Range("B2").Text & Mid(.FullName, InStrRev(.FullName, "."))
    End With
    
End Sub

 

[NVRensburg]

You're amazing thank you! That works!