Create New Sheets, Copy New Sheets & Name New Sheets - §8-O

I have a workbook with 52 tabs or so... one for each week of the year. It's a budget workbook.

Each year, I find myself having to copy this workbook, then rename all 52 tabs for the new week-ending dates. It's a chore, to say the least. What I would like to do is have a macro that would simplify this process. Rather than creating a workbook each year, I would hit the magic macro button and it would slap another 52 tabs into my current workbook and I could roll on with my budget.

I've found a macro that can name a sheet into date format. I've found one that will duplicate a sheet. I found one that will make new tabs based off of a list in a sheet. My problem is finding one that will do all of this or a macro that will make the other 3 macros run in the correct order.

I'm definitely confused when it comes to macro code and have no idea where to start on this.

I've attached my current workbook. Pay no attention to the first tab and the current names of the tabs ("PP Ending dd-mm"). This is just the way I've been naming them in the past.

Originally Posted by mikerickson

To be clear, do you want to add new tabs to an existing workbook, or do you want to rename the tabs of an existing workbook.
What would a typical tab name be?

I want to add new tabs to the existing workbook. As we move towards December this year, I want to be able to use the macro to add 52 more tabs for 2017 instead of me manually having to create them.

Typically, the tab names are "PP Ending 1-12", "PP Ending 1-19", etc. For my own personal use, the pay period ends on every Thursday. I don't have to name it like that, though. I could just have the date itself. "dd-mm-yyyy" would be great.

Do you think you might know of how to accomplish this?

Mikerickson... I was just thinking... Would it be better to try and code one macro or one that would make others run in succession?

Originally Posted by mikerickson

I was looking at your workbook and thinking...

The naming of the sheets should include year information. i.e. 'PP ending 2015-01-15' rather than 'PP Ending 1-15'
It would be a good plan to have a (hidden) Template sheet that would be used as the model for each of the sheets. You could have the formulas all set up and ready to go.
Sheets should be sorted in order of date.
It would also be a good idea to have a StartYear sheet before every year starts so that formulas could get YTD information. Possibly an End sheet as well.

With this many sheets in a workbook, you might also want a control sheet for either navigation or to hide the previous year's sheets.

A single macro "add new year's sheets" would be fairly easy.
Although an "add next week's sheet" would also be a workable approach.

I've got some notions and will post a suggestion soon.

I like your approach and ideas there. That seems to make much more sense. In regards to hiding the previous year's sheets... would it be possible to simply delete the old sheets once the new year's sheets were made? That might help cut down on the size some, especially after using it a few years.

I appreciate you putting this much thought in it. I've been trying to accomplish this for quite some time but haven't been able to figure it out. I'm anxious to see if it finally becomes a reality.

Originally Posted by mikerickson

This is what I have right now.

You'll notice the new sheet Control.
There are (abbreviated) instructions in A1:B2
On top of column A, there are some buttons.
In C4 and down and right, all the sheets are listed.
The red cells are hidden sheets, the grey cells are very hidden.
If you select some sheet names and right click, those sheets will be toggle between hidden and un-hidden.
If you right click on a single cell with a very hidden sheet, that will unhide that sheet. (To very hide a sheet select one cell and press the Very Hide button.)
If you double click on a cell with a visible sheet, you will be taken to that sheet.

The buttons:
Dress Column Widths - adjusts the column widths of Control sheet. (in progress)
Write Sheet Names - puts the names of the worksheets on the Control sheet. Its not really needed, but is useful until the workbook is finished.
Very Hide Selected Sheets - self explanatory.
Make New Sheets - that calls the routine that this whole thread is about.

If you want a Delete Sheets option, that would be best cast as another button on Control.

I hope this helps.

Wow! Very interesting! That's amazing that you can make Excel do that. In regards to the delete option... don't worry about that. I can delete them out easy enough manually. Just making the tabs and automatically naming them is a time saver! I'll have to play around with this for a little bit and get accustomed to it. I made it error out a couple times and had to start over. I was just testing how it reacted to various things.

I appreciate you taking the time to help out on this. I've been tweaking this workbook for several years now and I'm pretty close to making it be everything I always wanted. Some of my formula writing, as you can probably tell, is pretty rudimentary. I don't know a whole lot but can usually figure it out even if unconventional.

Anyhoo... If you have time, maybe give me a few tips or pointers on what to mess with and what not mess with in regards to clearing cells on the control sheet. That might aid me as I dig into this further and play around with it.

This definitely helps!

Thanks!!

Originally Posted by mikerickson

I was also looking at your worksheets.
(With an eye to suggesting that you put several weeks on a single worksheet rather than one week per sheet. (There are lots and lots and lots of empty cells in that workbook.) But your using this for several years suggests that changing the basic style isn't in the cards.)

The thing that I noticed is that there is a lot of resource intensive formatting on that sheet. Conditional formatting that is for appearance only.

I can't say that I disagree with you on any of that. This thing has developed into what it is today but the basic layout has never deterred. Projected stuff on the left and actual stuff on the right. I guess I made so many spaces in case I ever needed to expand. I've thought about redesigning the whole thing but it'll take that "Ah-Ha!" moment to pop up in my mind... Something that would really make me realize it could be so much better. As of now, I don't know how to improve it and still find it user-friendly. I've definitely thought about it though. If you have any ideas, I'd be glad to hear them.

I appreciate what you've done. I think the macros you designed will definitely help out with my current workbook. I'll be putting this to use for sure. Unless you have any other tricks up your sleeve, I'll consider this solved. You definitely accomplished what I was looking for and expanded on it.

Thank you!