auto adding sheet from template

[THX1138]

I have a code to automatically add sheets to a workbook populated with values from the first sheet, works great (thanks to HalfAce).

Now what I need is to have those new sheets that are generated come from, or have the same layout as, an existing sheet in the same workbook.

Currently they are just basic speadsheets with no formatting at all. What I want if the new sheets to have the macros and such that I have created in the original.

Thanks in advance.

 

[Norie]

Why not just copy the 'template' sheet?

And then use the existing code to do the rest.

 

[THX1138]

what do you mean "just copy the template"? I am trying to make sheets that auto populate based on a master sheet, then are in turn filled out in further detail. We used to use a template file, I am looking for a way to have the new sheets created by the code look, act and have all the tools of this original template file. Right now, the new sheets generated are bare, blank worsheets other than the 6 cells of text created by the code.

I want to use "the existing code" to create sheets that look like the original template.

I hope that makes it clear and I appreciate the help.

 

[Norie]

It really would help to see the existing code, or perhaps you could post a link to the original thread.

What I mean by 'copy the template' is basically copy the master sheet.

That should create a new sheet with all the formatting, coding and controls as the master.

You could then copy the required data into these new sheets.

 

[THX1138]

Yes, we have done exactlly that for some time. Fill out one sheet, copy it 2,3,10 times into the workbook depending on how many items we needed sheets for. Then go to each one and edit them as needed for each item.

Perhaps I need to clarify something . . .

When I refer to "template", I am referring to the template workbook we use to create a new document.

When I refer to "master sheet", I am referring to the first sheet in this template workbook, the master sheet didn't exist until I started this project to improve the system (I use the term system loosely) we use to create and distribute information.

What I am trying to do is create a master sheet that gets the basic info for each item on one sheet, then generates a seperate sheet for each item (or row). The new sheet is used to add other information. I could have 20 sheets in the template file all links to the first sheet, but we do not always need 20 sheets, In order to save file space, it would be great to have new sheets generated only when more sheets are needed.

When others review the workbook, the first sheet is like a summary of all the items detailed in the workbook, some need to review the individual sheets, others only need the basic info on the master sheet.

I hope that helps. Below is the code.

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        If Target.Column = 2 And Target.Row > 1 And Target <> vbNullString Then
            Dim ws As Worksheet
            Set ws = Me.Parent.Sheets.Add(After:=Me.Parent.Sheets(Me.Parent.Sheets.Count))
            With ws
                On Error Resume Next
                .Name = Target.Offset(0, -1)
                If Err.Number <> 0 Then
                    MsgBox "Unable to create new worksheet.", vbOKOnly + vbCritical, "Error"
                    Application.DisplayAlerts = False
                    .Delete
                    Application.DisplayAlerts = True
                    Set ws = Nothing
                    Exit Sub
                End If
                On Error GoTo 0
                .Range("A1:A6") = Application.Transpose(Array("PRIORITY", "PART NUMBER", "DESCRIPTION", "TYPE", "REQUESTED BY", "COMMENTS"))
                .Range("B1") = Target.Value
                .Range("B2") = Target.Offset(0, 1)
                .Range("B3") = Target.Offset(0, 2)
                .Range("B4") = Target.Offset(0, 3)
                .Range("B5") = Target.Offset(0, 4)
                .Range("B6") = Target.Offset(0, 5)
            End With
            Application.EnableEvents = False
            Me.Hyperlinks.Add Target.Offset(0, 7), Address:=vbNullString, SubAddress:="'" & ws.Name & "'!A1", _
                TextToDisplay:="PN SHEET"
            Application.EnableEvents = True
            Set ws = Nothing
            Me.Activate
        End If
    End If
End Sub

[/code]