Excel 2007 : Copying from multiple templates based on criteria.

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.

Option Explicit

Private Sub CreateSheets_Click()
Dim strCol As String
Dim strRow As String
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim strWsName As String
Dim strSrcName As String

On Error GoTo ErrHnd

'setup column letter and first row number containing names
'column
strCol = "Q"
'row (number is in double quotes)
strRow = "7"

'turn off screen updating to stop flicker & increase speed
Application.ScreenUpdating = False
            
'save this worksheet's name, so we can go back to it later
strSrcName = ActiveSheet.Name

'set start of data in selected column
Set rngStart = ActiveSheet.Range(strCol & strRow)
'find end of data in selected column
Set rngEnd = ActiveSheet.Range(strCol & CStr(Application.Rows.Count)) _
            .End(xlUp)

'loop through cells in used range
For Each rngCell In ActiveSheet.Range(rngStart, rngEnd)
    'ignore empty cells in range
    If rngCell.Text <> "" Then
        'get worksheet name
        strWsName = rngCell.Text
        'test if worksheet exists
        On Error Resume Next
         If Worksheets(strWsName) Is Nothing Then
            'worksheet does not exist
            'reinstate error handling
            On Error GoTo ErrHnd
            'copy worksheet named "Template"
            Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
            'name new sheet
            Worksheets(Worksheets.Count).Name = strWsName
            Else
            'worksheet already exists
            'reinstate error handling
            On Error GoTo ErrHnd
        End If
    End If
Next rngCell

'go back to the source worksheet
Worksheets(strSrcName).Activate

'reinstate screen updating
Application.ScreenUpdating = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
'go back to the source worksheet
Worksheets(strSrcName).Activate
'reinstate screen updating
Application.ScreenUpdating = True

End Sub

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

dim whichTemplate as worksheet

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)

set whichTemplate = thisworkbook.worksheets(thisworkbook.worksheets("Summary").range("A3").value)

sure, i suppose. attach away

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.

Worksheets(ThisWorkbook.Worksheets(strSrcName).Cells(rngCell.Row, 3).Value).Copy

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

SOlution attached.

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.

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

Private Sub CreateSheets_Click()
Dim strCol As String
Dim strRow As String
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim strWsName As String
Dim strSrcName As String
Dim i As Long

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

you are welcome, please mark the post as solved.

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