Deleting multiple sheets
 

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

Deleting multiple sheets
 

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

Deleting multiple sheets
 

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

Deleting multiple sheets
 

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

Deleting multiple sheets
 

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

Deleting multiple sheets
 

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

Deleting multiple sheets
 

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

Deleting multiple sheets
 

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

Deleting multiple sheets
 

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

Deleting multiple sheets
 

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

Deleting multiple sheets
 

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

Deleting multiple sheets
 

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.

--
Regards,
Tom Ogilvy


"Toppers" wrote:


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

Deleting multiple sheets
 

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