Forum Discussion
GENERATE A NEW JOB/PROJECT NUMBER WHEN YOU OPEN A SPREADSHEET
See if this does what you want:
Press Alt+F11 to activate the Visual Basic Editor.
Double-click ThisWorkbook in the Project Explorer pane on the left hand side.
Copy the following code into the module:
Private Sub Workbook_Open() With Worksheets(1).Range("B1") .Value = .Value + 1 End With End SubThis version places the job number in cell B1 on the first sheet. You can change that in the code if you wish.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Make sure that you allow macros when you open it.
See the attached sample workbook.
Is the job number simply a sequential number (1, 2, 3, ...)?
Or does it include the current year, e.g. 2021-001, 2021-002, ...?
Or does it include text characters - if so, what should it look like?
Hello Hans,
Thank you for responding to me. Yes you are correct, all that I want is simply a sequencial number so that every time I open the spreadsheet, it will generate the next sequencial job number.
See if this does what you want:
Press Alt+F11 to activate the Visual Basic Editor.
Double-click ThisWorkbook in the Project Explorer pane on the left hand side.
Copy the following code into the module:
Private Sub Workbook_Open() With Worksheets(1).Range("B1") .Value = .Value + 1 End With End SubThis version places the job number in cell B1 on the first sheet. You can change that in the code if you wish.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Make sure that you allow macros when you open it.
See the attached sample workbook.
- Hi Hans,
Many thanks for your help, works exactly as i want it to do in the document
