Macro to create new sheets with numbers in sequence

Hi to all,

I would like to apologize in advance if this has been posted already, I searched the topics to try find it but could not.

I currently have a spreadsheet with over 600 sheets all numbered sequentially and grows every month. On each sheet there are many details and formulas but most important for this issue is a cell (lets say it's A1 for the course of this thread) that has the same value as the sheet name. So if the sheet is named "523" then cell A1 should read "523".
Then I have a "Main" sheet that pulls values from the sheets based on the sheet number and dates.

Up until now I have been creating these sheets manually, however this month I have to add over 400 sheets to this spreadsheet and I simply do not have the time to do it manually.

Is there a macro that can be programmed to create a copy of the last sheet and name it as 1 more than the last sheet (if the last sheet is names "620" then the macro creates a copy of the sheet and names it "621") and then make the cell A1 on that sheet named as the sheet's name?

Otherwise I am not going to be able to cut it this month and will be mightily unpopular...

Thank you all in advance!
sheets.PNG

Hi canyflyer,

Welcome to the forum!!

Here's my attempt:

Please Login or Register  to view this content.

I would really question your layout though as Excel will sooner or later start having issues with this many tabs (if it doesn't already). Do you really need separate tabs or could you perhaps create a template that's populated by different rows from a master dataset (tab) say?

HTH

Robert

Is there a way of doing the same thing but instead of creating a blank sheet - copying the last one so that all the formulas are included as well and then renaming the sheet and cell A1 accordingly?

Yes:

Please Login or Register  to view this content.

Sorry, not Excel savy enough to know what "a template that's populated by different rows from a master dataset (tab)" is... :-S

Attach a sample workbook with just a few tabs, devoid of any sensitive data if need be, and I'll have a look for you.

Regards,

Robert

Hi canflyer,

Thanks for the feedback and adding to my reputation

Have a look at the attached where I've created a DataMatrix tab that the Template uses to fetch data from based on the drop-down in cell D2 of that tab (which is automatically refreshed each time it is selected). You can see it when you select between the two agreement numbers from the drop-down. I haven't coded the payment schedule.

The big advantage with this approach is that you'll only ever need two tabs!!

If you could please mark the thread as solved it would also be appreciated.

HTH

Robert

First off - thank you for introducing me to the drop down menu - I did not know of it till today. How do you insert and program it?

The Data Validation option is found on the Data Tools tab from the Data ribbon. As the data is in a different tab (DataMatrix) from the validation resides (Template) I created a named range that automatically sets itself each time the Template tab is selected.

Secondly - if I do go with this option how does the main spreadsheet pull through the values needed? As all details are under one sheet ("Template").

I created VLOOKUP formulas to return the data from the DataMarix tab based on the data validation selection I included in cell B2 of the Template tab.

HTH

Robert