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