Generate next number each time template opens

[RaceyBee]

Hi Guys and Gals,
This seems so simple but it is frustrating me beyond belief. I have a manual job sheet template with a job number in cell h2 (say 95004). Each time someone opens a worksheet using that template their worksheet job number should be increased by one. This involves recording the new job number back on the template so that the next sheet will use a new next number.

I have achieved it by doing a "save as" and saving the new sheet back over top of the original template, but that is messy and dangerous.

A better way would be to have another sheet that only contains the next number, such that when the template opens a macro runs to open the "next number sheet", add one to the number, both on the new worksheet and on the "next number sheet" and write the latter away, keeping the new worksheet to then be filled in, printed and saved.

Am I over complicating this? Any help will be appreciated.

Cheers,
RaceyBee

[tlafferty]

Is your template an XLTX file? If not, save it as an XLSM file, name the cell that contains the jobnumber you'd like to use "NextJob" and add the following code:

Private Sub Workbook_Open()
    If Not ThisWorkbook.ReadOnly Then
        Application.Range("NextJob").Value = Application.Range("NextJob").Value + 1
    Else
        MsgBox "This workbook is not the latest version so you may not be using the latest job number."
        ThisWorkbook.Close savechanges:=False
    End If
    
End Sub

[tlafferty]

Note: This approach assumes that you don't want to share the workbook and force users to have only the latest number. It also assumes, dangerously, that every user that opens the book will have their macros enabled by default. If they don't, it won't work.

[RaceyBee]

Thank you for your reply Thomas. My template is an XLTM (macro-enabled template) file.
There will be 4 users using the template and they will all be macro-enabled. They each need to access to same number sequence.

RaceyBee

[tlafferty]

I see a potential issue: when a template is used, a new file based on that template is created, which effectively cuts off the new file from its source, thus the issue you point out about needing to reference the original workbook. Take a look at the Input # and the Write # statements:

Dim MyString, MyNumber 
Open "TESTFILE" For Input As #1    ' Open file for input. 
Do While Not EOF(1)    ' Loop until end of file. 
    Input #1, MyString, MyNumber    ' Read data into two variables. 
    Debug.Print MyString, MyNumber    ' Print data to the Immediate window. 
Loop 
Close #1    ' Close file.

This needs modification of course, but I'm hoping it points you in the right direction.

[RaceyBee]

I don't really follow your latest suggestion. Is this to read data from another file which, in my case, would hold the last used number?

I got around my issue using your first method and adapting it. The template now does not hold a job number, I keep that in a separate sheet. When the user uses the template to set up a new sheet he/she then clicks on a "Job No." button which triggers a macro to read the Job no., add one to it and write it back. The new job no. is then used in the new worksheet.

Thanks for your help. You pointed me in the right direction.

Regards,
RaceyBee