Help required in creating macros for 3 things

hi there,

I think I'm in the right place to get some help with my excel problems I'm having?

I have a spreadsheet that will be built on throughout the year. When a new job comes into our team I would like the guys to be able to insert a new job with a template, plus there were some additional things that I have no idea whether they're possible or not so would love to see what you guys think.

Specifically, I would like to create a new sheet, after sheet 3 using the "template" and renaming it with the next number in the sequence (job numbers) and having it also create a link (with the sheet name) to the sheet within the index job number column.

Secondly, I would like to know if there is a way to populate the index based on cell information from the new jobs when entered?

Lastly, is finding out the correct and easiest way to automatically calculate totals from each sheet (same cell location in each sheet) that also take into account with new sheets are created.

Sounds extremely basic and I'm sure it is - I really haven't done much of this type of thing before but am planning on getting some training down the track. In the meantime, I have searched this site and others to try and get a fix but can't work it out so i look forward to see if you can help me.

I have attached an example spreadsheet outlining what I'm trying to achieve.

I realllllly look forward to your assistance and apologies if it's super basic. Example Spreadsheet.xlsm

I moved your instructions to cell N11 because I need the area under the first few columns blank. I had to delete your sheet 201503 to run the code and that screwed up the table to the right that you speak of. So just push the create job button and you'll see it creates the sheet 201503 and creates the hyperlink on the dashboard. Just let me know what else you need. As well as telling me what else you need, It might be a good idea to type or create stuff manually and highlight it so I see what it's supposed to be. After you create the new 201503 sheet you could do this with the table at the right so I know what you want.

Good Luck!!!

Thanks so much skywriter that worked fantastically.

Just wondering as an extension on this, if I were to hide the 'template' sheet but still want the macro to run as you have set it up as - with the new sheet being visible as normal, how would I go about that?

Originally Posted by tahnimaree

Thanks so much skywriter that worked fantastically.

Just wondering as an extension on this, if I were to hide the 'template' sheet but still want the macro to run as you have set it up as - with the new sheet being visible as normal, how would I go about that?

Try this version if you want to hide the template.

Well I would guess it has something to do with you giving me one sheet to write code against and then using the code on another sheet. In your picture I don't see college anywhere and I don't see regional anywhere. So I don't even know where that is coming from.

You should make a copy of the actual workbook and then delete out the confidential information and then post it.
Don't change any sheet names or move anything or delete any sheets, leave it as is, just remove the data. I'm sure I can easily adjust the code. It probably has to do with a difference in sheets, or columns.

College is in A31 and Regional is listed twice in A32 and A33.

Duh, sorry it's almost 1:00 A.M. here, I'm about ready for bed. My mind was telling me this had something to do with the code trying to name a sheet something other than what my original code wanted it to. Anyway I see those now.

I think that has something to do with named ranges. I see in your sheet that I have that these are named ranges, but there's a #REF error. Go into formulas tab, then name manager and tell me in the refers to column what it says these two names refer to in your sheet.

Yeah it has to do with some named ranges you have. I don't know the history of these, so if you want to explain it to me I can help. But if you go to formulas, name manger you will see quite a few and they all have ref errors. If you don't need these you could just delete them and that might stop the errors.

If you just try to make a copy of the template sheet, which is exactly what the code does, then you will see that this same error comes up. Is has to do with those names being a part of or referenced on the template I don't know which and when you try to make a copy you get the name issue.

No sheet, it's sorted out? Okay, thanks for the feedback.