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

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

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

--
Regards,
Tom Ogilvy


"hshayh0rn" wrote:

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

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

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

--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"hshayh0rn" wrote:

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

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 ;-)

Hi Tom,

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"hshayh0rn" wrote:

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

Put END IF before NEXT WKS statement


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


"hshayh0rn" wrote:

Hi Tom,

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"hshayh0rn" wrote:

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

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

Jim Thomlinson


"Toppers" wrote:

Put END IF before NEXT WKS statement


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


"hshayh0rn" wrote:

Hi Tom,

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"hshayh0rn" wrote:

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

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!



"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Toppers" wrote:

Put END IF before NEXT WKS statement


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


"hshayh0rn" wrote:

Hi Tom,

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"hshayh0rn" wrote:

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

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

--
HTH...

Jim Thomlinson


"hshayh0rn" wrote:

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

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

"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"hshayh0rn" wrote:

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