Copying worksheet and +1 on job number

MonkeyDeath

Board Regular
Joined
Jul 19, 2007
Messages
62
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Hi
insert the following code into your existing macro
Code:
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top