adding a sheet - format

THX1138

New Member
Joined
Feb 2, 2003
Messages
46
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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Can't you just copy that sheet? Or am I missing something here?
 

THX1138

New Member
Joined
Feb 2, 2003
Messages
46
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.

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

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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

New Member
Joined
Feb 2, 2003
Messages
46

ADVERTISEMENT

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

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
here's a code snippet:
Code:
    Worksheets("Template").Copy after:=Worksheets("Report")
    Set wsNewWorksheet = ActiveSheet
    wsNewWorksheet.Name = "Copy" & varname
... see if that makes any sense.
 

THX1138

New Member
Joined
Feb 2, 2003
Messages
46
that created a copy of the worksheet in the workbook, but did not populate it from the values on the title sheet.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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.
 

Forum statistics

Threads
1,137,367
Messages
5,681,076
Members
419,950
Latest member
BeckiJae

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top