Excel VBA - Sheet Name Wild Card Search

Question

I have a workbook with lot of sheets and pivot tables. All the sheets with Pivot Tables have Pvt Tbl in the sheet name. Now I want to clear data in all the sheets except sheets with Pivot Table and Control Sheet (Where I have all my buttons for operations) I have the following code so far but it doesn't seem to be working. I also want make "Control" sheet only visible and rest of the sheets should be hidden. I am doing a wild card search in sheet names by using Like Statement with "*Pvt Tbl" but it still deletes Pivot Tables in all worksheets. Any help is appreciated. Thanks in advance!!

Sub ClearData()

Application.ScreenUpdating = False

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    If Not ws.Name Like "Control" Or ws.Name Like "*Pvt Tbl" Then ws.Cells.Delete: ws.Cells.Delete
    If Not ws.Name Like "Control" Then ws.Visible = False
Next

Application.ScreenUpdating = True

End Sub

Tmdean Tmdean · Accepted Answer · 2015-03-26T18:16:38

Think about the condition you're writing.

If Not ws.Name Like "Control" Or ws.Name Like "*Pvt Tbl"

If the name is not Control OR the name ends with "Pvt Tbl", ...

Sounds like you want the condition to instead be:

If the name is not Control AND the name does not end with "Pvt Tbl", ...

If Not ws.Name Like "Control" And Not ws.Name Like "*Pvt Tbl"

It's easy to forget that computers will always do exactly what you tell them to do.

This is one of the conditionals that might be more clear through the application of De Morgan's laws. A logically equivalent condition that might be easier to understand is:

If Not (ws.Name Like "Control" Or ws.Name Like "*Pvt Tbl")

Excel VBA - Sheet Name Wild Card Search

1 Answers