Copy a sheet, rename it, or delete it based on cell value.

[ML43s52]

I need your expertise with the following scenario.
Start out with only two (2) sheets named Master and Sheet2.
Code and calculations will reside on the sheet named Master.
Cell B13 = sum of cells B3 through B12
If the value of cell B13 changes then run a code to do the following….
If cell B3 = 1 then make a copy of Sheet2 and rename it with the value in cell C3 (Priority 1).
If cell B3 changes from a value of 1 to 0 then delete the sheet with the name in cell C3 (Priority 1).
If a sheet with the name in cell C3 (Priority 1) already exists then skip and go to next.
Perform the same functions for cells B4 through B12.



A B C1
2
3 1 Priority 1
4 1 Priority 2
5 0 Priority 3
6 0 Priority 4
7 0 Priority 5
8 1 Priority 6
9 1 Priority 7
10 0 Priority 8
11 1 Priority 9
12 0 Priority 10
13 5

 

[Fluff]

Are the values in B3:B12 being input manually?

 

[ML43s52]

No, the B3:B12 contain formulas.

Are the values in B3:B12 being input manually?

 

[Fluff]

Ok, how about

Private Sub Worksheet_Calculate()
   Dim Cl As Range
Application.ScreenUpdating = False
   For Each Cl In Range("B3:B12")
      If Evaluate("isref('" & Cl.Offset(, 1).Value & "'!A1)") Then
         If Cl.Value = 0 Then
            Application.DisplayAlerts = False
            Sheets(Cl.Offset(, 1).Value).delete
            Application.DisplayAlerts = True
         End If
      ElseIf Cl.Value = 1 Then
         Sheets("Sheet2").Copy after:=Sheets(Sheets.count)
         ActiveSheet.Name = Cl.Offset(, 1).Value
         Me.Activate
      End If
   Next Cl
End Sub

This needs to go in the sheet module for the "Master" sheet

 

[ML43s52]

Thank you, thank you, thank you.

I was banging my head against a brick wall all weekend trying to get this to work, you did it in a blink of an eye.

Greatly appreciated.

Ok, how about

Private Sub Worksheet_Calculate()
   Dim Cl As Range
Application.ScreenUpdating = False
   For Each Cl In Range("B3:B12")
      If Evaluate("isref('" & Cl.Offset(, 1).Value & "'!A1)") Then
         If Cl.Value = 0 Then
            Application.DisplayAlerts = False
            Sheets(Cl.Offset(, 1).Value).delete
            Application.DisplayAlerts = True
         End If
      ElseIf Cl.Value = 1 Then
         Sheets("Sheet2").Copy after:=Sheets(Sheets.count)
         ActiveSheet.Name = Cl.Offset(, 1).Value
         Me.Activate
      End If
   Next Cl
End Sub

This needs to go in the sheet module for the "Master" sheet

 

[Fluff]

Glad to help & thanks for the feedback