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

#### ML43s52

##### New Member
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

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Fluff

##### MrExcel MVP, Moderator
Are the values in B3:B12 being input manually?

#### ML43s52

##### New Member
No, the B3:B12 contain formulas.

Are the values in B3:B12 being input manually?

#### Fluff

##### MrExcel MVP, Moderator
Code:
``````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
Sheets(Cl.Offset(, 1).Value).delete
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

##### New Member
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.

Code:
``````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
Sheets(Cl.Offset(, 1).Value).delete
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

##### MrExcel MVP, Moderator
Glad to help & thanks for the feedback

Replies
3
Views
88
Replies
2
Views
291
Replies
4
Views
686
Replies
15
Views
587
Replies
1
Views
383

1,136,314
Messages
5,675,032
Members
419,544
Latest member
MVPDoug

### 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.

### Which adblocker are you using?

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

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