Forum Discussion
GENERATE A NEW JOB/PROJECT NUMBER WHEN YOU OPEN A SPREADSHEET
Hi Everyone,
I am new to this forum so please be patient with me. I am currently been given the task to create a new work/time sheet for when doing field engineering work. The job sheet i have created needs to generate a new job number everytime the spreadsheet is opened.
Does anyone know if have the capability to auto generate what we call a new job number everytime the spreadsheet is opened?
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.
5 Replies
Actually, i shouldn't reply to such messages.
No information on the Excel and operating system version.
No inserted file (without sensitive data).
No detailed / exact explanation, should the sheets get a number or should a number appear somewhere on a sheet? ... if where?
But I have a heart for stressful people who feel they are facing an insurmountable wall.
I hope you got the info for your next message :).
If not ... take a look here ...
Welcome to your Excel discussion space!
(Who can read is an advantage 🙂
Here is an example with the information you have provided (or not).
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
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.
