Excel crashes when running this VBA

[bbalch]

I'm using Microsoft 365 for Business - Excel version 2107

I have a workbook with multiple tabs and have the code below that deletes worksheets that are not listed in the array. I run this code on a monthly basis to generate reports, and the code has worked without any problems in the past.

I'm trying to run the code now and it causes Excel to crash. It will enter the For Loop and delete 2 or 3 sheets and then it crashes. Any ideas, on what is causing the problem?

The day, filepath, and f_name are global variables outside of this sub.

Option Explicit

Public day As String
Public filepath As String
Public f_name As String


Sub run()

Dim wb As Workbook
Dim myfilename As String
Dim Sht As Worksheet
Dim Arr As Variant
Dim wsName As Variant
Dim Matched As Boolean

    myfilename = filepath & f_name & "Shared Other - " & day & ".xlsx"
    ' open the workbook and pass it to workbook object variable
    Set wb = Workbooks.Open(myfilename)

    Application.ScreenUpdating = False

    ' Delete Tabs that are not listed 
    
    Arr = Array("22-420 Other Special Acti", "22-425 Touring Awards", "22-430 Triple Play Awards", "22-440 Board")
    Application.DisplayAlerts = False
    
    For Each Sht In Worksheets
        If Not UBound(Filter(Arr, Sht.Name, True, vbTextCompare)) >= 0 Then Sht.Delete
    Next Sht
    
    Application.DisplayAlerts = True

' Save and Close Workbook
wb.Save
wb.Close

End Sub

 

[bobsan42]

I take it you run the code from another workbook.
First I would recommend to always be explicit about what you want to do:

For Each Sht In wb.Worksheets

Reduce unneeded complications JIC:

Set wb = Workbooks.Open(myfilename, False)
Application.Calculation = xlCalculationManual

Also be aware that Filter returns partial matches as well.
Very Hidden sheets cannot be deleted.
If the number of sheets is not too great you can try to run the code in debug mode and try to find out if the crash appears always at a certain sheet or a random one every time. Or you can temporarily leave the alerts on. Also sometimes the actual operations happening in the background cannot catch up with the code speed - doevents may help in similar cases.
There are really many things that can go wrong so you need to narrow it down.

 

[bbalch]

This code is saved in my personal macro workbook and yes I do execute the code on another workbook.

I appreciate your recommendation and will make those updates.

I'm not exactly sure what was causing the problem but I think I found a temporary solution. I tried running the code on another machine and it worked properly. So it's some type of issue isolated to my machine. I disabled all of the Excel Add-Ins that were loaded and the code works properly after that. It appears that there is some type of conflict with one of the add-ins.

 

[Gokhan Aycan]

Just a note, you are not switching Application.ScreenUpdating = False back to True. Intentional?