Creating Required Worksheets from Various Templates

I have seen some examples of creating worksheets from master templates.
Bue my situation is a liitle more complex.
Here's what I envision the final product to look like.

The workbook would have a number of different templates for various products (approx. 12). These would be hidden.

When you open the workbook, you would see a main page with some basic project information and a list of available templates [no tabs yet]. The user would select the templates he wants and how many of each. The macro would then create the number of appropriate worksheets [tabs] and arrange them in the order in which they were listed on the main page. The user would then go to each tab and start to fill out /update each one and save the workbook under the project name.

Any guidance or help would be greatly appreciated.
modytrane.

Hello modytrane,

I am not sure I understand what you what to do. It seems you want to make copies of the hidden sheets that the user can then modify and save those in the same workbook. Is that correct? The reason I ask is because a true Template is a workbook with an .xlt extension and not simply a worksheet.

Sincerely,
Leith Ross

You are correct.
I used wrong terminology.
I want some standard blank worksheets [formatted, filled in with tables, titles etc.] hidden.
When the user selelcts how many of what type, then the macro would copy those types of sheets and create tabs in the workbook.
The use can then go ahead and fill out those blank sheets and save the workbook as a project.
Next time the user needs to start a new project he would open the master copy and create worksheets as required for that project.
Thank you for your response.
I hope you can help me with some guidance and sample code.
I can expand and customize the code after that.
Modytrane.

Hello Modytrane,

I have attached an example workbook. This has a user form that is displayed when the workbook is first opened. There are 2 hidden sheets that you can select from a list box. When the user clicks on "Create Project" a new workbook is created with these sheets in it and named using the text entered into the text box on the user form.

Sincerely,
Leith Ross

Thanks a lot.
I will need some time to work on this and modify to suit my needs.
It looks like something that I had envisioned.
Two modifications I will need to make are:
1. Provide a place for quantity of each type of sheets. In my project some sheets will be needed more than once. So the userform will need to have a list of sheets and Qty. as well.

2. I will also need to provide a way to add sheets after the project has been created. This way if a project needs to be modified, the user should be able to add or delete sheets.

If you can provide some code for either of these, it would be great.

Thanks a lot for your help.
modytrane.

Hello modytrane,

With the multiple copies of a sheet, will the number of sheets be fixed or will the user be able to change it? Say for instance, you "Sheet1" and it is used 3 times. Would the macro copy that sheet 3 times or would the user be allowed to change it from 3 to something else?

Sincerely,
Leith Ross

The usebox would have a list of sheets and Qty to be filled in.
Something like:

_ Sheet 1 _ Qty
_ Sheet 2 _ Qty
_ Sheet 3 _ Qty

The user would check off the sheets he needs and fill in Qty amounts.
The macro would then copy the selected sheets and repeat each sheet based on Qty. entered for that sheet. Sot the workbook may have the following:

Sheet1, Sheet1, Sheet1, Sheet2, Sheet3, Sheet3.

The user had selelcted Sheet 1 (Qty.3), Sheet2 (Qty. 1), and Sheet 3 (Qty. 2).

Thanks,

modytrane

Hello ModyTrane,

I have added the ability to make multiple copies of a sheet. Have a look and let me know if this what you want.

Sincerely,
Leith Ross

Hello Leith,
I really appreciate your time and expertise.
The sample you have created is very much like what I want to create.
I will need to read up on the Userforms and learn about them before I can customize it. I found following link that shows simple steps to create userforms. Do you have any other suggestions, so I can understand the userforms better?
I am the type, who preferes to learn and understand rather than just take a ready made solution. That way I can support it and build on it for future use.
The sample you provided will do what I want, but I will need to add a feature to be able to add or delete sheets from previously created projects.

http://www.contextures.com/xlUserForm01.html


One more question relating to userforms if you don't mind.
I have another excel spreadsheet that has one worksheet [Database], which five different users update. They simply add or modify up to 20 different columns across a row. This worksheet has some formulas [VLOOKUP], drop down menus and some calculations. I set it up that way, so the names they enter are from named list. That way they are consistent and help me in my reports. Each row represents a project name and its related attributes such as sales rep name, Dollar amount, contractor's name, estimator's name etc. Then I have 15 other worksheets that create various reports from this database. The five users can't modify these reports, they can only read and print them. The datatbase worksheet has over 300 rows and its growing. Everything is working fine. But sometimes, the users don't follow the rules and they delete, copy or paste data and mess up conditional formatting or other calulations.
My question is: Is this the most effective use of excel spreadsheet or should I look in to creating userform for them to enter data. If yes, then does the userform allow me to create a form with 20-25 fields and then append the Database worksheet? That way the users can only fill out forms for new projects or modify existing records. The useform would then transfer the information to the worksheet. All other reports would work the same way.

I know its a long message. I am not in any rush, but would appeciate your guidance.

Thanks again,
modytrane