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!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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
Back
Top