Copying worksheet and +1 on job number

[MonkeyDeath]

im creating a Job sheet each work book will have all the jobs for that month

what im trying to do is have a master worksheet called master that is blank,

then whe i finish the jobsheet click a button and it prints that sheet, locks ALL the cells, copys the master sheet and gives it a new number witch is basically +1 of the one ive just printed, and renames the tab with that number.

the printing, locking the cells and copying the worksheet is fine thats not a problem, its making the job number +1 of the previous job sheet automatically with out any human input. ive cheated with the tabs where when you copy a tab its adds Job (3) in brackets and im using that for my job number in the tabs

 

[ravishankar]

Hi
insert the following code into your existing macro

Worksheets(Sheets.Count).Name = "Job # " & Sheets.Count

It will add a number to the job tab( same as sheet number). you can add + or -1 depending on what you want your job no to be
Ravi

 

[MonkeyDeath]

thanks for that,

to help me learn what does that do, can you brake down the string and explain so i can understand what the commands mean?

 

[ravishankar]

Hi
Sheets.count counts the number of sheets in your workbook. so the equation reads as
worksheets(8).name = "job # " & 8 it simply means the name of 8th sheet is = & joins the text job # and the sheet number. There you go.
Ravi

 

[MonkeyDeath]

right ive just added it and run it, i can see waht it does, it counds all the work sheets including the new one adds them together puts that number in the tab with a prefix of "job" before it.

what i didnt write/say was that there is 2 pages (overall report of the month and printing costs) needed to be in the workbook as well so it adds them too, so im always 2 job numbers ahead.

im not too fussed now about the job number being ni the tab, as when i copy the wooksheet its puts (#) after the word job (of the worksheet it is copying) so this is a lazymans get around.

what im trying to work out is how to have the main job number in the worksheet +1 when i copy my template to be the next job number

so far i have a button that when clicks it;

saves the workbook
prints the current worksheet
protects all the cells in the current worksheet
puts the job number, client name, Client code and project name in month report
copys the tempate to have a new job sheet ready

 

[MonkeyDeath]

Hi
Sheets.count counts the number of sheets in your workbook. so the equation reads as
worksheets(8).name = "job # " & 8 it simply means the name of 8th sheet is = & joins the text job # and the sheet number. There you go.
Ravi

ahh so if i did worksheets(sheet.count-2).name~ it would - the 2 pages i have ot have in there

 

[MonkeyDeath]

oops

 

[MonkeyDeath]

right ive used your code it doesnt do what i was hoping,

im not going to have the jobnumber specifically in the tab, because when you copy the wooksheet it put (#) in there and i can use that

but how do i get it so that when i copy the wooksheet the job number goes up by 1