Deleting sheets not working 2

[willydude]

This is the kind of problem that drives me crazy. The following works. It deletes all sheets that have the name sheet.

Sub DeleteWorksheets()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim sheet As Worksheet
For Each sheet In ActiveWorkbook.Worksheets
    If InStr(1, LCase(sheet.Name), "sheet") Then
        sheet.Delete
    End If
Next sheet
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

The following should delete all sheets EXCEPT the ones named "OriginalData" and "SalesPeople".

Sub CleanCommissionsBySalesBreakDown()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim sheet As Worksheet
'On Error Resume Next
For Each sheet In ActiveWorkbook.Worksheets
    If Not InStr(1, LCase(sheet.Name), "OriginalData") Or
Not InStr(1, LCase(sheet.Name), "SalesPeople") Then
    sheet.Delete
    End If
Next sheet
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

The above proc deletes "OriginalData" and
"SalesPeople".

I'm sure that it's simple, but....

 

[PHV]

Replace the Or with And

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[willydude]

Still wants to delete "OriginalData".

I've checked the spelling of the sheet name. It
is accurate. I've switched the order of "OriginalData" and "SalesPeople" in the code.

It's got to be something simple, but not so obvious.

 

[PHV]

You may try this:
If InStr(LCase(sheet.Name), "originaldata") + InStr(LCase(sheet.Name), "salespeople") = 0 Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CBasicAsslember]

InStr is a function that returns a value. You have to test the value it returns.

 

[willydude]

This proc works.

Sub DeleteUnneededWorksheetsSalesPeopleAndDataFromMonthlyCopy()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks("CommsForTesting.xls").Activate
Dim sheet As Worksheet
For Each sheet In ActiveWorkbook.Worksheets
    If InStr(1, sheet.Name, "SalesPeople") Or _
        InStr(1, sheet.Name, "OriginalData") Then
        sheet.Delete
    End If
Next sheet
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

This one doesn't.

Sub CleanCommissionsBySalesBreakDown()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Workbooks("CommissionsBySalesQBTestFile.xls").Activate
Workbooks("CommsForTesting.xls").Activate
Dim sheet As Worksheet
For Each sheet In ActiveWorkbook.Worksheets
    If Not InStr(1, sheet.Name, "SalesPeople") Or _
        Not InStr(1, sheet.Name, "OriginalData") Then
        sheet.Delete
    End If
Next sheet
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

With the exception of the NOT keyword, I don't see any difference.

 

[PHV]

Again, replace the Or with And
BTW, have you tried my suggestion stamped 17 Jan 07 17:05 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

Sorry, hit submit too fast !
The negation of
A Or B
is either
Not A And Not b
or
Not (A Or B)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HughLerwill]

In such cases it can be good to initially formulate the problem in the positive sense;

If (A=1 Or B=2) then DoStuff

which is relatively easy to get your head around.

If you don't want stuff to happen (in the exactly opposite sense) then add a NOT;

If Not(A=1 Or B=2) then DoStuff

Hugh,

 

[willydude]

I have tried both of the following. Both sets of code WANT to delete "SalesPeople" and/or "OriginalData". They should NOT.

For Each sheet In ActiveWorkbook.Worksheets
    If Not InStr(1, sheet.Name, "SalesPeople") Or _
        Not InStr(1, sheet.Name, "OriginalData") Then
        'With ActiveSheet
        sheet.Delete
        'End With
    End If
Next sheet

and

For Each sheet In ActiveWorkbook.Worksheets
    If Not InStr(LCase(sheet.Name), "SalesPeople") Or _
        Not InStr(LCase(sheet.Name), "OriginalData") Then
        With ActiveSheet
        sheet.Delete
        End With
    End If
Next sheet

The following, which is for cleaning up a different wb does work as it should. It DOES delete the two sheets as requested.

For Each sheet In ActiveWorkbook.Worksheets
    If InStr(1, sheet.Name, "SalesPeople") Or _
        InStr(1, sheet.Name, "OriginalData") Then
        sheet.Delete
    End If
Next sheet

 

[PHV]

OK, I give up. Seems that you make absolutely no sense with the solutions suggested ...

 

[willydude]

PH,

On this one, I have given up also (for the time being).

I have tried the AND and the OR. I have checked my spelling. I do not see how the InStr
will work, but the Not InStr will not.

Thanks for your efforts.

Bill

 

[HughLerwill]

When you use NOT you should be aware that its argument is a boolean value otherwise you may (will) get unexpected (for you) results.

So you are using it with instr as in

Not InStr(LCase(sheet.Name), "SalesPeople")

; supposing InStr returns

0; NOT 0 will return -1 (true)
1; NOT 1 returns -2 (true)
2; NOT 2 returns -3 (true)
etc.
NOT n will only return a zero (False) when its argument is -1 and that is not going to happen when using Instr because it only returns values 0 and positive values.

If you must use NOT with Instr then you should wrap the Instr as follows;

Not (InStr(LCase(sheet.Name), "SalesPeople")=0)

or conversly;

Not (InStr(LCase(sheet.Name), "SalesPeople")>0)

Please read up vba help on the use of logical operators.

HTH Hugh

 

[HughLerwill]

re my 'should be aware that "

read 'should ensure that'

 

[HughLerwill]

Of course InStr(LCase(sheet.Name), "SalesPeople") will always return zero, suggest you try;

InStr(LCase(sheet.Name), "salespeople")

 

[PHV]

HughLerwill, I've already posted that ...

 

[HughLerwill]

PHV, sorry I missed that...
Think I may give up too!

 

[willydude]

This SKIPS over ALL sheets, including the ones I want to delete. No sheets are deleted.

For Each sheet In ActiveWorkbook.Worksheets
     If Not (InStr(LCase(sheet.Name), "SalesPeople") = 0) Or _
        Not (InStr(LCase(sheet.Name), "OriginalData") = 0) Then
'        With ActiveSheet
        sheet.Delete
 '       End With
    End If
Next sheet

This wants to delete ALL sheets, including “SalesPeople” and “OriginalData” that I do NOT want to delete.

For Each sheet In ActiveWorkbook.Worksheets
     If Not InStr(LCase(sheet.Name), "SalesPeople") Or _
        Not InStr(LCase(sheet.Name), "OriginalData") Then
'        With ActiveSheet
        sheet.Delete
 '       End With
    End If

This also want to delete All sheets

For Each sheet In ActiveWorkbook.Worksheets
     If Not (InStr(LCase(sheet.Name), "SalesPeople") > 0) Or _
        Not (InStr(LCase(sheet.Name), "OriginalData") > 0) Then
'        With ActiveSheet
        sheet.Delete
 '       End With
    End If

As to giving up, I leaning that way myself. I'm heading to lunch for now.

Thanks.

Bill

 

[PHV]

have you tried my suggestion stamped 17 Jan 07 17:05 ?

 

[WinblowsME]

Sub CleanCommissionsBySalesBreakDown()
   Dim sheet As Worksheet
   
   Application.ScreenUpdating = False
   Application.DisplayAlerts = False
   
   'Workbooks("CommsForTesting.xls").Activate
   
   For Each sheet In ActiveWorkbook.Worksheets
      If Not (InStr(1, sheet.Name, "SalesPeople") > 0 Or _
              InStr(1, sheet.Name, "OriginalData") > 0) Then
         sheet.Delete
      End If
   Next sheet
   
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True
End Sub