adding a sheet - format

[THX1138]

when adding a sheet to a workbook, is it possible to have excel use a different sheet than the default?

I have a sheet with macros, etc. This sheet is in a template workbook, when I add a sheet to that workbook can I use the existing sheet as the "template" for the new sheet?

I need this done within the template file, other users will not have the same version of MS Office and it would get messy to go to every workstation and change defaults and so on.

it would help to have the adding sheet done with a control button.

Thanks in advance.

 

[GlennUK]

Can't you just copy that sheet? Or am I missing something here?

 

[THX1138]

what i want to do ultimately is:
1. open the template file, fill out a title page (sheet 1) with rows 2 - 15 used to enter info on an individual item. (part number, description, type, etc.). And I have this done.

2. As the rows of info are filled out, automatically a new sheet is created in the same workbook when a vlaue is added to the 6th cell. I have this, entering info in the 6th cell executes a macro to add a sheet, this new sheet has the values just added to the title page. Also names the sheet so they are created as 1, 2, and so on. Very cool. When its done, it will be a workbook with a title page having the basic info and sheets for each item having more detailed info.

This is the part I cannot figure out.
3. The new sheets created by the title sheet need to have dropdown menus, further actions to be determined based on the individual item, macros for "snedmail", etc. Lots of stuff. These individual sheets get more info and can be distributed seperately as is often needed. Right now the new sheets are just the default worksheet. I need the new sheets to use a specific sheet format.

Here is the code.

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 '.Offset(0, 1) = vbNullString _'
            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, 6), Address:=vbNullString, SubAddress:="'" & ws.Name & "'!A1", _
                TextToDisplay:="PN SHEET"
            Application.EnableEvents = True
            Set ws = Nothing
            Me.Activate
        End If
    End If
End Sub

Hope this helps
Thanks

 

[GlennUK]

And why can't you have a sheet set up ready to be copied as this new sheet? It could be deleted or hidden after processing.

 

[THX1138]

that would be fine, but I do not know how to code it to use a specific sheet for the copies.

 

[GlennUK]

here's a code snippet:

    Worksheets("Template").Copy after:=Worksheets("Report")
    Set wsNewWorksheet = ActiveSheet
    wsNewWorksheet.Name = "Copy" & varname

... see if that makes any sense.

 

[THX1138]

that created a copy of the worksheet in the workbook, but did not populate it from the values on the title sheet.

 

[GlennUK]

It's a code snippet ... it's just to show how you copy a sheet instead of adding a sheet. So that you can see how to change your code ... it's not a replacement for your code.