VBA I need help with my code that deletes sheets

[Kra]

Hi all,

I am trying to create code that will delete all sheets in active workbook if name contains part text from input. So far nothing seems working and I have no cue why,


I am using this code:

Private Sub CommandButton28_Click()

    Dim shName As String
    Dim xName As String
    Dim xWs As Worksheet
    Dim cnt As Integer
    shName = Application.InputBox("Enter the specific text:", "Delete sheets", _
                                    ThisWorkbook.ActiveSheet.Name, , , , , 2)
    If shName = "" Then Exit Sub
    xName = "*" & shName & "*"
'    MsgBox xName
    Application.DisplayAlerts = False
    cnt = 0
    For Each xWs In ThisWorkbook.Sheets
        If xWs.Name Like xName Then
            xWs.Delete
            cnt = cnt + 1
        End If
    Next xWs
    Application.DisplayAlerts = True
    MsgBox "Have deleted " & cnt & " worksheets", vbInformation, "Sheets removed"


End Sub

But when I enter specific text (no spaces before or after) it doesn't work. Any ideas how to fix it?

 

[HaHoBe]

Hi Kra,

code works like expected for me in my test.

Ciao,
Holger

 

[Kra]

Hi Kra,

code works like expected for me in my test.

Ciao,
Holger

Ok, then I am even more confused why it is not working for me

 

[HaHoBe]

Hi Kra,

on my test I created a new workbook, creating a couple of new sheets, renaming some (one as the sampe you showed "PP uuu"). I ran the test with entering PP and the one sheet was gone. Any new sheets inserted are called Tabelle & i for me so I ran a test with e and all but one were deleted (one had been renamed to aaa).

I would take some precautions as entering and asterisk should delete any sheet in the workbook but there must be at least one sheet being left in the workbook.

even more confused why it is not working for me

Best for me in cases like that which I encountered more than once was to go out for a walk or set the new date for working on the problem to be the next day, in other words do something else that has little (idealy nothing) to do with Excel. Sometimes it is spending some time and a solution will show up. As you have not reported any error messages most of what I can think of to be the reason for not deleting sheets is not fitting here, sorry.

Holger

 

[Fluff]

Is the code in the same workbook as the sheets to be deleted?

 

[Kra]

Is the code in the same workbook as the sheets to be deleted?

Hi Fluff,

it is in user form in Personal.xlsb

 

[Kra]

Is the code in the same workbook as the sheets to be deleted?

I see what you are pointing, I changed ThisWorkbook to ActiveWorkbook and it works!

Thank you Fluff!

 

[Fluff]

Glad you sorted it & thanks for the feedback.