Delete specific sheets if criteria in cell met

ag235

New Member
Joined
Apr 3, 2012
Messages
10
Hello,
I've been searching for hours but haven't been able to find a code that focuses on what i am trying to do.

I have a drop down menu in cell F11, sheet name is "Select Product".
I would need a macro that would delete specific named sheets if a certain product is selected (sheet names "PST" and "Job Aid").

Any help would be greatly appreciated!

Thanks!
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

hatstand

Well-known Member
Joined
Mar 17, 2005
Messages
762
Office Version
  1. 2016
Platform
  1. Windows
Here you go.

Sub DeleteASheet()
Dim strName As String

On Error GoTo Nosheet
strName = Sheets(1).Range("A1")
Application.DisplayAlerts = False
Sheets(strName).Delete
Application.DisplayAlerts = True

Exit Sub
Nosheet:
MsgBox "The sheet " & Sheets(1).Range("A1") & " does not not exist"
Application.DisplayAlerts = True
End Sub
 

ag235

New Member
Joined
Apr 3, 2012
Messages
10
thanks for the reply but am confused as to how to apply it to my file.

this is a code that seems to work up until i have a second condition (i.e. when not CIB or Please select product), i get an error on this line: Worksheets("Data requirements", "Results", "Consult", "Turn down", "SLA", "Turndown Job Aid").Delete

This is the code:

Sub Delete()
Dim w As Worksheet
Application.DisplayAlerts = False
For Each w In Worksheets
If w.Name = "Select Product" And w.Range("F11") = "CIB" Then
Worksheets("PST by Risk").Delete
If w.Name = "Select Product" And w.Range("F11") <> "CIB" And w.Range("11") <> "Please Select Product" Then
Worksheets("Data requirements", "Results", "Consult", "Turn down", "SLA", "Turndown Job Aid").Delete
End If
Next
Application.DisplayAlerts = True
End Sub

thanks!
 

hatstand

Well-known Member
Joined
Mar 17, 2005
Messages
762
Office Version
  1. 2016
Platform
  1. Windows
Hi,

The code I posted is based on Sheet 1 cell a1 having the sheet name you would like deleted. So if you have a drop down in that cell and you chose ABC then ran the code. Sheet ABC will be deleted.

If you would like the sheet deleted when you have chosen. The code can be placed inside a change event.

Am I missing something?
 

ag235

New Member
Joined
Apr 3, 2012
Messages
10
the sheets won't have the same name as the options in the drop down.

if CIB is select, there are 2 sheets to be deleted with distinct names
if anything is select, there are about 5 sheets to be deleted with distinct names
 

hatstand

Well-known Member
Joined
Mar 17, 2005
Messages
762
Office Version
  1. 2016
Platform
  1. Windows
If you are choosing a word from a drop down. I would imagine somewhere you must have a list of worksheets that are 'attached' to that word. i.e. a lookup. If that's the case I would put the code inside a loop to look at the first word in the list and work down. That would apply the code and delete the relevant worksheets.

Am I on the right track.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,747
Messages
5,597,901
Members
414,187
Latest member
pdida8

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