Run Script when Criterion are Met

[Pestomania]

I have worked out a lot of the below VBA to run a duplicate when criterion are met. Can you help me get the VBA to run when certain criterion are met:

Where column C is not blank and column E is blank. I will activate it with a button click.

In this example, the script would only run on row 3-4.

Sub Duplicate_Business_Process()

Dim wsNSN As String 'This indicates the Worksheet Name found in Column B of wsBPT
Dim BPI As String 'This is the Business Process Identifier found in Column A of wsBPT
Dim Trigger_Row As String
Dim wsNBP As Worksheet 'This indicates the new worksheet being created for the new Business Process
Dim wsBPT As Worksheet 'This indicates the sheet that holds the business process tables
Dim wb As Workbook
Dim wsTemp As Worksheet 'This indicates the "Template" worksheet.

Dim BPRow As String, idCol As String, SheetNameCol As String, LinkCol As String, BPCol As String

Set wb = ActiveWorkbook
Set wsBPT = wb.Worksheets("Input Business Processes Tables")
Set wsTemp = wb.Sheets("Template")

idCol = "A"
SheetNameCol = "B"
BPCol = "C"
LinkCol = "E"

'Right here is where the system should check if C2 is not blank and where E2 is blank, run script on each row in wsBPT until criteria is no longer valid. Column A & B will be hidden

                wsNSN = wsBPT.Range("B2").Value
                BPI = wsBPT.Range("A2").Value
             
                wsTemp.Copy Before:=Sheets(4)
                Sheets(4).Name = wsNSN
                Sheets(wsNSN).Range("A2") = BPI
             
            Set wsNBP = ActiveSheet
             
                wsBPT.Activate
                wsBPT.Range("E2").Select
                    Selection.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
                        "'" & wsNBP.Name & "'" & "!B2", TextToDisplay:= _
                         wsNSN
             
                MsgBox wsNSN

End Sub

Identifier	New Sheet Name	Business Processes	Process Description	Link to Sheet
BCP-7009 - BP1	BCP-7009 - BP1 (Talking to Cow)	Talking to Coworkers	Talking with coworkers	BCP-7009 - BP1 (Talking to Cow)
BCP-7009 - BP2	BCP-7009 - BP2 (Talking to Cow)	Talking to Coworkers
BCP-7009 - BP3	BCP-7009 - BP3 (Talking to Cow)	Talking to Coworkers
BCP-7009 - BP4	BCP-7009 - BP4 ()
BCP-7009 - BP5	BCP-7009 - BP5 ()
BCP-7009 - BP6	BCP-7009 - BP6 ()
BCP-7009 - BP7	BCP-7009 - BP7 ()
BCP-7009 - BP8	BCP-7009 - BP8 ()
BCP-7009 - BP9	BCP-7009 - BP9 ()
BCP-7009 - BP10	BCP-7009 - BP10 ()
BCP-7009 - BP11	BCP-7009 - BP11 ()
BCP-7009 - BP12	BCP-7009 - BP12 ()

 

[Rhodie72]

Hope this helps a bit:
You could implement simple if..then..else...end if routines or a more useful select case... case (whatever variable you specifiy).... case else... end select variatioin that allows far more flexibility
in choices available

Sub Duplicate_Business_Process()

Dim wsNSN As String 'This indicates the Worksheet Name found in Column B of wsBPT
Dim BPI As String 'This is the Business Process Identifier found in Column A of wsBPT
Dim Trigger_Row As String
Dim wsNBP As Worksheet 'This indicates the new worksheet being created for the new Business Process
Dim wsBPT As Worksheet 'This indicates the sheet that holds the business process tables
Dim wb As Workbook
Dim wsTemp As Worksheet 'This indicates the "Template" worksheet.

Dim BPRow As String, idCol As String, SheetNameCol As String, LinkCol As String, BPCol As String

Set wb = ActiveWorkbook
Set wsBPT = wb.Worksheets("Input Business Processes Tables")
Set wsTemp = wb.Sheets("Template")

idCol = "A": SheetNameCol = "B": BPCol = "C": LinkCol = "E"

'Right here is where the system should check if C2 is not blank and where E2 is blank, run script on each row in wsBPT until criteria is no longer valid. Column A & B will be hidden
    Select Case IsEmpty(Range("C2").Text)
    Case False
        Select Case IsEmpty(Range("E2").Text)
        Case True
            With wsBPT
                wsNSN = .Range("B2").Value
                BPI = .Range("A2").Value
            End With
            wsTemp.Copy Before:=Sheets(4)
            With Sheets(4)
                .Range("A2") = BPI
                .Name = wsNSN
            End With
        
            Set wsNBP = ActiveSheet
        
            With wsBPT
                .Activate
                .Range("E2").Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
                    "'" & wsNBP.Name & "'" & "!B2", TextToDisplay:=wsNSN
            End With
        End Select
    Case True
        MsgBox "C2 wasn't empty: " & Range("C2").Text
    Case Else
    End Select
    MsgBox wsNSN

End Sub

 

[Pestomania]

Hope this helps a bit:
You could implement simple if..then..else...end if routines or a more useful select case... case (whatever variable you specifiy).... case else... end select variatioin that allows far more flexibility
in choices available

Sub Duplicate_Business_Process()

Dim wsNSN As String 'This indicates the Worksheet Name found in Column B of wsBPT
Dim BPI As String 'This is the Business Process Identifier found in Column A of wsBPT
Dim Trigger_Row As String
Dim wsNBP As Worksheet 'This indicates the new worksheet being created for the new Business Process
Dim wsBPT As Worksheet 'This indicates the sheet that holds the business process tables
Dim wb As Workbook
Dim wsTemp As Worksheet 'This indicates the "Template" worksheet.

Dim BPRow As String, idCol As String, SheetNameCol As String, LinkCol As String, BPCol As String

Set wb = ActiveWorkbook
Set wsBPT = wb.Worksheets("Input Business Processes Tables")
Set wsTemp = wb.Sheets("Template")

idCol = "A": SheetNameCol = "B": BPCol = "C": LinkCol = "E"

'Right here is where the system should check if C2 is not blank and where E2 is blank, run script on each row in wsBPT until criteria is no longer valid. Column A & B will be hidden
    Select Case IsEmpty(Range("C2").Text)
    Case False
        Select Case IsEmpty(Range("E2").Text)
        Case True
            With wsBPT
                wsNSN = .Range("B2").Value
                BPI = .Range("A2").Value
            End With
            wsTemp.Copy Before:=Sheets(4)
            With Sheets(4)
                .Range("A2") = BPI
                .Name = wsNSN
            End With
       
            Set wsNBP = ActiveSheet
       
            With wsBPT
                .Activate
                .Range("E2").Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
                    "'" & wsNBP.Name & "'" & "!B2", TextToDisplay:=wsNSN
            End With
        End Select
    Case True
        MsgBox "C2 wasn't empty: " & Range("C2").Text
    Case Else
    End Select
    MsgBox wsNSN

End Sub

This looks like it would do what I am wanting on just row 2, which is my fault, I didn't specify better. I would need it to cycle from row 2 to 3 to 4 and so on until the criterion is no longer met. I am guessing that would be something to the effect of using "i" variable but I can't recall how to use it properly

 

[Rhodie72]

This looks like it would do what I am wanting on just row 2, which is my fault, I didn't specify better. I would need it to cycle from row 2 to 3 to 4 and so on until the criterion is no longer met. I am guessing that would be something to the effect of using "i" variable but I can't recall how to use it properly

With 1 criterion it is simple, but with 2 or more criteria it gets a litle complex. Select case is invaluable for quick edits to code.

 

[Pestomania]

With 1 criterion it is simple, but with 2 or more criteria it gets a litle complex. Select case is invaluable for quick edits to code.

I can create a validator column that has formula to say "If this is a Yes", then run the code. I am open to suggestions.