Delete specific sheets if criteria in cell met

[ag235]

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!

 

[hatstand]

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]

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]

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]

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]

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.