Duplicate Sheet When Complete

[Pestomania]

Hello!!

I have the below sheet that I am using to "trigger" actions and I would like some help.

Trigger: When button1 is clicked, create a new sheet where Business_Process_List[Link to Sheet] = "" (is empty) & Business_Process_List[Business Processes] <> "" (contains a value)

Action 1: Make a copy of sheet named "Template" (this sheet is hidden at all times)
Action 2: Get value from Business_Process_List[New Sheet Name] (which will be hidden) & name new sheet with this name
Action 3: Get value from Business_Process_List[Identifier] (which may be hidden) & place it in the new sheet created under cell A2 (which will be hidden)
Action 4: Input a "Link to Sheet (cell B1)" in Business_Process_List[Link to Sheet]
Action 5: Lock the Business_Process_List[Business Processes] value that was just created (I don't want it to be edited once someone types it in and the new sheet has been created).

Loop this action until all Triggers are completed and no further Business Processes exist without a link.

If you have any efficiency recommendations, I am interested in them!

Here is my origin table:

Book1
	A	B	C	D	E
1	Identifier	New Sheet Name	Business Processes	Process Description	Link to Sheet
2	BCP-7009 - BP1	BCP-7009 - BP1 (Talking to Cow)	Talking to Coworkers	Talking with coworkers
3	BCP-7009 - BP2	BCP-7009 - BP2 ()
4	BCP-7009 - BP3	BCP-7009 - BP3 ()
5	BCP-7009 - BP4	BCP-7009 - BP4 ()
6	BCP-7009 - BP5	BCP-7009 - BP5 ()
7	BCP-7009 - BP6	BCP-7009 - BP6 ()
8	BCP-7009 - BP7	BCP-7009 - BP7 ()
9	BCP-7009 - BP8	BCP-7009 - BP8 ()
10	BCP-7009 - BP9	BCP-7009 - BP9 ()
11	BCP-7009 - BP10	BCP-7009 - BP10 ()
12	BCP-7009 - BP11	BCP-7009 - BP11 ()
13	BCP-7009 - BP12	BCP-7009 - BP12 ()
14	BCP-7009 - BP13	BCP-7009 - BP13 ()
15	BCP-7009 - BP14	BCP-7009 - BP14 ()
16	BCP-7009 - BP15	BCP-7009 - BP15 ()
17	BCP-7009 - BP16	BCP-7009 - BP16 ()
18	BCP-7009 - BP17	BCP-7009 - BP17 ()
19	BCP-7009 - BP18	BCP-7009 - BP18 ()
20	BCP-7009 - BP19	BCP-7009 - BP19 ()
21	BCP-7009 - BP20	BCP-7009 - BP20 ()
Input Business Processes Tables

Cell Formulas
Range	Formula
A2:A21	A2	=Dashboard!$B$2&" - BP"&ROW([@[Business Processes]])-1
B2:B21	B2	=IF(LEN([@Identifier] & " ("& [@[Business Processes]]&")")>31,LEFT([@Identifier] & " ("& [@[Business Processes]]&")",30)&")",[@Identifier] & " ("& [@[Business Processes]]&")")

Named Ranges
Name	Refers To	Cells
Business_Process_Identifier_List	=Business_Process_List[Identifier]	B2

 

[Pestomania]

I was able to work out a script to create the duplicate worksheet, which is below. How can I design the trigger to work properly?

Sub Duplicate_Business_Process()

Dim New_Sheet_Name As String
Dim Business_Process_Identifier As String
Dim Trigger_Row As String
Dim new_Business_Process As Worksheet


New_Sheet_Name = Worksheets("Input Business Processes Tables").Range("B2").Value
Business_Process_Identifier = Worksheets("Input Business Processes Tables").Range("A2").Value

Sheets("Template").Copy Before:=Sheets(4)
Sheets(4).Name = New_Sheet_Name
Sheets(New_Sheet_Name).Range("A2") = Business_Process_Identifier

Set new_Business_Process = ActiveSheet

Worksheets("Input Business Processes Tables").Activate
ActiveSheet.Range("E2").Select
    Selection.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'" & new_Business_Process.Name & "'" & "!B2", TextToDisplay:= _
         New_Sheet_Name

MsgBox New_Sheet_Name

End Sub