Excel 2007 : Copying from multiple templates based on criteria.

[Pololuck]

I have a summary page where you enter item numbers followed by the item name. I have a worksheet used as a template which I named template >.<. I have a command button that when pressed will make a new sheet copied from the template and name the sheet according to the item. My question is, how can I modify this code to copy from different templates based on criteria that I input next to the item. Say I have Item 1 in A1, Item Name in A2 and Item type in A3. Right now the macro will name the worksheet depending on A2 but will just copy the template worksheet. One more thing, how do I change this code so that I can copy from hidden worksheets? I'm new here so I hope I posted this right.

Please Login or Register  to view this content.

[GeneralDisarray]

i'd suggest declaring a worksheet variable for ease of use:

Please Login or Register  to view this content.

then to point that variable to the right template use this line below (i can't tell which sheet contains the selection - for the purpose of this answer i will assume it's in a sheet named "Summary" and the template selection is sitting in cell a3 of that sheet)

Please Login or Register  to view this content.

[Pololuck]

I just started using excel about 3 months ago, if I attach the workbook, do you think you can rewrite the code? I have no idea how to write code, I copied that code from a forum . Thank you for the quick reply though.

[GeneralDisarray]

sure, i suppose. attach away

[Pololuck]

Here is the attached workbook. If I hide the template it won't copy.

[GeneralDisarray]

Only change (other than setting your start row to 4) was the following line -- I used this in place of the call to "Template" you had in the attached sheet.

Please Login or Register  to view this content.

As long as the C column contains the template name, this will work for you.

SOlution attached.

[Pololuck]

Thank you thank you, one more question though. What do I change for it to copy from these templates if they are hidden?

[Pololuck]

I saw this code in a forum but how would I apply it to my workbook?

Please Login or Register  to view this content.

[GeneralDisarray]

Easiest thing to do was just to unhide all the sheets, run the code, then rehide the templates..

The macro in the attached sheet will do this. Try it out.

[Pololuck]

I get a compile error, variable not defined and it points to the i in
[code]
For i = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Worksheets(i).Visible = True
Next i[code]

[Pololuck]

Sorry about last post, I dont know what happened

Please Login or Register  to view this content.

[GeneralDisarray]

huh, how the heck did that happen...possibly uploaded the wrong sheet. Here's the fix (there was no declaration for the 'i' variable).

Please Login or Register  to view this content.

That last declaration clears things up....attached is corrected sheet.

[Pololuck]

Thank you for your time. I appreciate all the help you've given me.

[GeneralDisarray]

you are welcome, please mark the post as solved.

---edit---
ha ha, you already marked it! my mistake