Add new worksheets depending on a list with predetermined names

[Masun]

Hi all,

I donŽt have the knowledge to create this by myself so im hoping for your great help again, if this is possible.

I have a workbook with two sheets, "Start" and "RefTab". I need a macro that adds or delete sheets depending on how many numbers there are in a list in sheet "RefTab. I also need that the new sheets are named Cabin 1, Cabin 2 up to the number in the list.

I would like every new sheet to be a copy of "Start" sheet, except that one specific cell (B6) need to have the same number as the number of the sheet. So if I have Cabin 10 i need B6 to be 10, and in Cabin 17 B6 should be 17. When I start a new project there may be a shorter list, fewer cabins. So if possible I need the macro to delete sheets named Cabin XX if they are a bigger number then the listnumber.

Is this possible? I sure would be very grateful!

Hope you understand what i am trying to achieve. Please se attached file.

/Masun

[HaHoBe]

Hi, Masun,

put this procedure and function into a normale module and see if it does what you want:

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

Ciao,
Holger

[Masun]

Holger!

Thank you very much for helping me out! I m really greatful!

Its almost perfect. If you could assist me to perfection it would be super! It seems like the macro make one sheet to little. Instead of 27 new sheets in the uploaded file it produced 26. 26 is good but it would be great with 27 if its not to much to ask for. Another thing i wonder about is why the active cell in all sheets is DY935322? Is it possible to make it A1 instead?

Once again, many thanks!

/Masun

[HaHoBe]

Hi, Masun,

yeah, youŽre right about the last sheet being deleted.

Please Login or Register  to view this content.

Ciao,
Holger

[Masun]

Hi Holger!

Thank you very much for your effort here. You are really close now. It works perfect when adding rows in the table in sheet Reftab. But if i then delete one row in the table in sheet Reftab and then run the macro it once again is one sheet "short".

You said

yeah, youŽre right about the last sheet being deleted

I wonder if the other thing, that about the active cell in all sheets is DDY935322, isnt happening for you? It is for me im afraid...

/Masun

[HaHoBe]

Hi, Masun,

regarding the active cell in all sheets: should have been corrected by adding the line

Please Login or Register  to view this content.

if I had written True like here (instead it was Trrue which should raise an exception). You could use CTRL+HOME to go all the way up.

regarding the deleting of names: the "curse" of lists.

Please Login or Register  to view this content.

Ciao,
Holger

[Masun]

Hi Holger,

It seems like the curse of lists is quite powerful, when i run this i get 24 new sheets instead of the 27 im hoping for. The problem with the active cell is fixed, probably it was solved and i missed it, sorry for that - my bad. But can you overcome the powerful curse of the list? If so you have a new title; Lord of the List... ;-)

[HaHoBe]

Hi, Masun,

not so much a curse but more not so good programming skills from my side.

Please Login or Register  to view this content.

Ciao,
Holger

[Masun]

Holger!

I am happy to tell you that you now are Lord of the Lists. ;-)

You are almost a little bit too humble ;-)

Thank you very much for your help!

/Masun

[Masun]

Holger,

This is just a question regarding of design (at least thats what i think), all the functionality is working as i want it to and im really greatful for that. But just for the look of the sheetnames...is there a way to avoid the new sheets to be named Cnt 001, Cnt 002...Cnt 027 and instead be named Cnt 1, Cnt 2, Cnt 27? I am guessing there is a reason that you named them that way?

/Masun

[HaHoBe]

Hi, Masun,

I am guessing there is a reason that you named them that way?

Me? Thinking ahead for anything? YouŽre kidding

Reason for this is when you try to sort the sheets inside a workbook. As the sheetnames with the numbers would be handled as a string the order after sorting should be Cnt1, Cnt10, Cnt11... Responsible for the sheet naming is this line of code

Please Login or Register  to view this content.

Any number will be formatted with leading zeros to match three digits. If you want just the plain numbers behind Cnt like "Cnt 1" you should change that line to read

Please Login or Register  to view this content.

or even omitt the blank after Cnt to get Cnt1 as resulting sheetname.

HTH,
Holger

[Masun]

Holger,

Thanks for all the help and this last explanation!

/Masun

[Masun]

Holger (or anyone else if she or he would like to help...)

I kept on using the file after all your help yesterday and i realize that if i add (manually) a new sheet with some calculation or something, i once again get one less "new" sheet than i need to. If i then add one more sheet manually for some other calculation or something im getting two sheets short when i run the macro. Is there a way to just handle the Cnt 001 -XXX -sheets, and leave other sheets out? I appologize that i didnt think of this earlier, it seems like i have a problem to think ahead of things... ;-)

/Masun

[HaHoBe]

Hi, Masun,

the original file had two sheets which where to be added to the number of sheets in the workbook resulting in

Please Login or Register  to view this content.

You would need to adapt the 2 to the number of sheets excluding Cnt sheets (as well as 3 to number of those + 1) or use a constant in the beginning and read the code like

Please Login or Register  to view this content.

Or you would loop through all sheets and see it only those with Cntx are to be deleted if not in the list any more - then you would be free of altering any constants.

Ciao,
Holger

[Masun]

Hi Holger,

I think i need the macro to loop through all sheets and just handling the sheets who will be added or deleted, the Cntx:s. All other sheets, like those two from the orignal file or any other sheet that might manually be added should be "left alone" if you understand what i mean. This would help a lot instead of changing the constant in the code as soon there has been a new sheet (not those CntX) added or removed. Hope you can help me with such a loop.

/Masun

[HaHoBe]

Hi, Masun,

maybe like this:

Please Login or Register  to view this content.

Ciao,
Holger

[Masun]

Hi Holger,

Thank you for your struggle to help me. I really appreciate it.

Where in the "original" code do i put this?

/Masun

[HaHoBe]

Hi, Masun,

I would not work that into the code but keep it as an extra macro. If you want to delete all sheets CntX you may either call the last macro prior to running the code or just enter a call to it right near the top of the original macro code:

Please Login or Register  to view this content.

HTH,
Holger

[Masun]

Hi Holger,

I probably express myself poorly, or i dont understand how you mean here. If i run the version 5 of your code it works like i want it to. I can then change the Const clngNUMBER As Long = 2 to whatever I like. And if i have 2 sheets (that are not Cnt:s) like in the original file the macro works. If i then change the constant to 3 and add a sheet manually and name the new sheet to "yaddayadda" the macro still works. But if i then add another sheet "yaddayadda2" without changing the constant the macro will add or remove one sheet to little. My thing is that i rather dont want to change the constant to adjust to the number of sheets that i always want to have in the file, sheets like "start" and "reftab" and some sheets i might add and remove in the future, like "yaddayadda".

As it is right now with 2 as the constant and two sheets ("start" and "reftab") the macro works, it places out as many sheets as there are numbers in the table in sheet "reftab" and names them Cnt 00X. Perfect. But, if possible, it would be super if there was some kind of smart way to handle the situation where another sheet (not the Cnt:s but manually added sheets like "yaddayadda") have been added without have to change the constant number.

Do you see what im aiming for, are you ahead? ;-)

/Masun

[HaHoBe]

Hi, Masun,

I got the point. In the previous macros I had tried not to loop thorugh all the sheets but I think we will have to take that route (compare each name if left three characters is Cnt to the list) to instead of just deleting sheets from the highest index on down.

IŽll be back after some serious testing by this evening.

Ciao,
Holger

[HaHoBe]

Hi, Masun,

depending on the format that you apply to the names for the new sheets please check the following code (please mind that only the new sheet may have Cnt on the left of them):

Please Login or Register  to view this content.

Ciao,
Holger

[Masun]

Holger,

Perfect!! Once again, thank you very much!

Masun