Excel VBA - Wild Card Search Sheet Title

I have a book with a lot of sheets and pivot tables. All sheets with pivot tables have Pvt Tbl in the sheet name. Now I want to clear the data on all sheets except sheets with a pivot table and a control sheet (where I have all my buttons for operations). I have the following code so far, but it does not seem to work. I also want the "Control" sheet to be only visible, and the rest of the sheets should be hidden. I do a search on different cards in sheet names using the Like statement with "* Pvt Tbl", but still delete pivot tables on all sheets. 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
+4
source share
1

, .

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

Control "Pvt Tbl",...

, , :

Control AND, "Pvt Tbl",...

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

, , .

, . , :

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

All Articles