auto adding sheet from template

THX1138

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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Why not just copy the 'template' sheet?

And then use the existing code to do the rest.
 

THX1138

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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

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

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
            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]
 

Forum statistics

Threads
1,136,348
Messages
5,675,241
Members
419,555
Latest member
Paddington

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