Assistance with Excel Macro

Hi everyone, my first post.......

This forum has been extremely helpful as a novice excel user and I hope someday I can return the favour and help somebody else.

I have been trying to get some macros to work for and workbook we use to auto generate a job number in our work shop for a job card we use. Up until now it's been working well, where it simply generates the next job number and auto saves to file as that generated number into one folder location on our server.

I'd like to now take this to the next level whereby it does what I just mentioned, but would like it to create a folder of the job number it just generated and save that file into that new folder. I have spent days looking through these forums, as well as Google but can't seem to find anything that works, and I just seem to get errors on what I try.

The code I am currently using the generate the next job number and auto save the file with that number into a single folder location is as follows:

This is the workbook code:

Option Explicit

Private Sub Workbook_Open()

    Dim strFileName As String

    With Sheet1.Range("C3")
        .Value = .Value + 1
        strFileName = .Value
    End With
    Me.Save
    
    'Me.SaveAs Filename:="C:\Temp3\" & strFileName
    
    Run "Create_Invoice"
    
    'optional code to close the main workbook after creating the invoice
    Me.Close

End Sub

This is the Module1 code:

Option Explicit

Sub Create_Invoice()

    Dim strFileName As String

    strFileName = Sheet1.Range("C3").Value

    ActiveWorkbook.Worksheets.Copy
    'the new workbook is now active
 
    With ActiveWorkbook
        Sheets(1).Name = strFileName
        .SaveAs Filename:="H:\JOB_FILES\CURRENT_JOBS\" & strFileName
    End With

End Sub

Any assistance and guidance would be greatly appreciated.

Regards,
Matt

Originally Posted by humdingaling

all you need to add is MKDIR command to make the directory
http://www.techonthenet.com/excel/formulas/mkdir.php

not sure where along the line you want to plonk it in

probably just before the save as in the create invoice macro?

Many thanks for your reply and feedback. How does this create a new directory for the file name that my original code has created ?? i.e; my code will generate a new job card/file with the next number, but I need it to now create new folder with that job number as its name and save file into it.

You my friend are an absolute legend !!! It works, thank you very much, I greatly appreciate this, thank you !

Done. Again thanks for your help. I'm learning slowly and hopefully one day I can be of help to someone else.

Cheers....