Deleting multiple sheets

[hshayh0rn]

I have some code that I thought would delete every sheet in a workbook except
the two master sheets that can't be deleted but it's not working. I'm
thinking the problem is with the OR part of the code. If I remove the OR then
the code runs but the OR is pretty important so I'm look for some insight.

Sub DeleteSheets()
Dim wks As Worksheet
Application.DisplayAlerts = False
For Each wks In Worksheets
If wks.Name <> "Product Names Data" Or "Master Product Sheet" Then
wks.Delete
Next wks
ErrorHandler:
Application.DisplayAlerts = False
End Sub

 

[Kruncher]

Edit this to read:

If (wks.Name <> "Product Names Data") Or (wks.Name <> "Master
Product Sheet") Then
wks.Delete

and you should be OK.

Kruncher

 

[Tom Ogilvy]

If you use an OR, then every sheet will result in a true value

Master1 <> Master1 or Master1 <> Master2

False or True = True

with OR, Master1 <> Master2 would be true. Use AND instead:

false And True = False

Sub DeleteSheets()
Dim wks As Worksheet
Application.DisplayAlerts = False
For Each wks In Worksheets
If wks.Name <> "Product Names Data" AND _
"Master Product Sheet" Then
wks.Delete
Next wks
ErrorHandler:
Application.DisplayAlerts = False
End Sub

 

[Jim Thomlinson]

I think you need an And condition so that if it is not this sheet and it is
not that sheet then delete the sheet. Boolean is a little funny when you use
not =

Sub DeleteSheets()
Dim wks As Worksheet
Application.DisplayAlerts = False
For Each wks In Worksheets
If wks.Name <> "Product Names Data" and wks.Name <> "Master Product
Sheet" Then
wks.Delete
Next wks
ErrorHandler:
Application.DisplayAlerts = False
End Sub

 

[Kruncher]

At least when I make a mistake first thing on a Monday, I was corrected
by the best. Thanks Tom.

 

[Jim Thomlinson]

Don't you need to include the sheet reference in your second condition?

If wks.Name <> "Product Names Data" AND _
wks.Name <> "Master Product Sheet" Then

 

[hshayh0rn]

Hi Tom,

I'm getting a next without for error if I copy your code.

 

[Kruncher]

Yes, you're correct. I did show it but missed the AND/OR problem. Tom
missed the second reference, so I don't feel so bad now ;-)

 

[Toppers]

Put END IF before NEXT WKS statement


"Master Product Sheet" Then
wks.Delete
End If
Next wks

 

[Jim Thomlinson]

It is hard to tell when code is posted but I think that the end if is not
required because the if statement was intended to be all on line. If the
delete is a new line then I would agree that the end if is necessary. Hard to
tell.

 

[Toppers]

Jim,
You are right .... I made the judgement that wks. delete was a new
line and therefore the END IF was required: but I did also recognise that it
could be one line. As Tom rarely makes a mistake, the one line view is most
likely!

 

[Tom Ogilvy]

I make plenty of mistakes - and feel comfortable there are plenty to correct
them if I do - and I don't intend every answer to be a turnkey solution. I
actually didn't look beyond the logic of the OR conjunction as the OP
intimated that the code was working but having problems with the OR statement
logic.

Sub DeleteSheets()
Dim wks As Worksheet
Application.DisplayAlerts = False
For Each wks In Worksheets
If LCase(wks.Name) <> "product names data" And _
LCase(wks.Name) <> "master product sheet" Then
wks.Delete
End If
Next wks
ErrorHandler:
Application.DisplayAlerts = True
End Sub

worked for me.

 

[hshayh0rn]

Thanks Jim and everyone else for your responses. I got it to work so thank you!