Consecutive numbering

[Lella]

I've designed a Job sheet for work where all the information of certain jobs
are saved. I need to be able to open the template and have the consecutive
number of the sheet change. i.e. If my first job sheet is labeled as Job #
929 the next time I open the template I need it to read # 930. I'm completely
confused.

 

[Dave O]

This type of thing can be done- the problem is arranging it to work
only once. If you open the template and the job number advances by one
and then you save the template, if we're not careful the next time you
open the file it will advance by one again, and create a lot of
confusion. So in order to create a bulletproof solution, can you
please describe how you will be using these files? For instance: you
have a Job sheet master template; you'll open it, use it, apply job
information, save it under a different file name, refer to it
periodically, etc.

 

[Lella]

This type of thing can be done- the problem is arranging it to work
only once. If you open the template and the job number advances by one
and then you save the template, if we're not careful the next time you
open the file it will advance by one again, and create a lot of
confusion. So in order to create a bulletproof solution, can you
please describe how you will be using these files? For instance: you
have a Job sheet master template; you'll open it, use it, apply job
information, save it under a different file name, refer to it
periodically, etc.

That's exactly right. I'll be saving it under the actual job number and email that sheet to specified depts where it will be opened and changes may be made and saved again. The next time I open the original template it will be for a different job. I hope this clarifies it a bit.

 

[Lella]

This type of thing can be done- the problem is arranging it to work
only once. If you open the template and the job number advances by one
and then you save the template, if we're not careful the next time you
open the file it will advance by one again, and create a lot of
confusion. So in order to create a bulletproof solution, can you
please describe how you will be using these files? For instance: you
have a Job sheet master template; you'll open it, use it, apply job
information, save it under a different file name, refer to it
periodically, etc.

 

[Dave O]

I've figured out some code to make this work- but I need to know: is
there a cell in your Job sheet that is *** ALWAYS *** blank in the
template and that is *** ALWAYS *** completed when you apply job data
to a job? The date? The client name? Anything like that will do.

 

[Dave O]

Forgot to mention: I need the tab name and cell address, too.

 

[Lella]

I've figured out some code to make this work- but I need to know: is
there a cell in your Job sheet that is *** ALWAYS *** blank in the
template and that is *** ALWAYS *** completed when you apply job data
to a job? The date? The client name? Anything like that will do.

Yes. The sheet just has basic headings ie Job #, Date, Show name etc. that are completed every time I fill it out.

 

[Lella]

Forgot to mention: I need the tab name and cell address, too.

I don't know if this is what you're referring to but I-3 is where the Job number would be. Is that right?

 

[Dave O]

Not quite- but we'll get there. Here's what I need you to do, starting
with step 0 because I forgot to make this step 1:
0. Create a backup copy of your template, so you can recover data in
case of a catastrophe.

1. Open the template and create a named range called JobNumber (spelled
just like that) by clicking on the menu *Insert *Name *Define. In the
top box type JobNumber and in the bottom box type =930 (or whatever
your current job number is). Click OK.

2. In cell I3, the job number cell, enter the formula =JobNumber and
the current job number will appear.

3. With the template showing on screen, press ALT-F11 to display the
Visual Basic editor. There is a project pane on the left side of the
window; in that pane your template's file name is displayed in bold
font. Below that is an icon for This Workbook. Right-click This
Workbook and select View Code. Copy and paste this code into the pane
on the right:

Private Sub Workbook_Open()
If Range("sheet1!c3").Value = "" Then Names("JobNumber").Value =
Evaluate(Names("JobNumber").Value) + 1
End Sub

4. Notice this part of the code: Range("sheet1!c3")
Change the sheet1 to match the tab name of your sprdsht, and change c3
to the client name's cell or another cell that is blank in the template
but that will be filled in for a job.

5. Note the job number displayed in I3, save the file, close it, open
it again, and you should see the job number has incremented by one.

6. Fill in a dummy name or whatever in the cell you indicated in step
4, save the file as though it was an actual job, and reopen. You
should see the job number has NOT incremented by one.

Let me know how it goes!

 

[Dave O]

Note: the newsgroup may have word-wrapped this on your screen:
If Range("sheet1!c3").Value = "" Then Names("JobNumber").Value =
Evaluate(Names("JobNumber").Value) + 1

Make sure this is all on one line in the Visual Basic editor.

 

[Lella]

Note: the newsgroup may have word-wrapped this on your screen:
If Range("sheet1!c3").Value = "" Then Names("JobNumber").Value =
Evaluate(Names("JobNumber").Value) + 1

Make sure this is all on one line in the Visual Basic editor.

IT WORKED! IT WORKED! Thank you so much. You've lifted a great burden off my shoulders.

 

[Dave O]

Wow, first time out? Cool! Have a great weekend.

 

[usinyorkshire]

Wow, first time out? Cool! Have a great weekend.

Hi Dave O.

Any chance you could help me with this? Prob is im using Excel 2010 and cant seem to sort it. Any help greatly appreciated.

Knightrider