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

ML43s52

New Member
Joined
Jul 22, 2014
Messages
9
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
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows
Are the values in B3:B12 being input manually?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
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
            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

New Member
Joined
Jul 22, 2014
Messages
9
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.
39665596_217533205774554_8870279137613316096_n.png


Ok, how about
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
            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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Forum statistics

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