Run-time error '1004':

[bbalch]

I have some VBA that has worked in the past without a problem but will not run anymore. The VBA deletes all the sheets in a workbook that are not listed. If searched the forums and there are several posts that suggest replacing "Sheets" with "Worksheets", but this did not work for me. The error message I receive is "Run-time error '1004': Method 'Sheets' of object_Global' failed"

Sub DelSheets()

    Dim Arr As Variant
    Dim Sht As Variant
    
    Arr = Array("SUMMARY", "General", "INDUSTRY INITIAT", "SPECIAL PROJECTS", "CHRISTMAS SPEC", "AWARDS SHOW", "Music Fest")
    
Application.DisplayAlerts = False
    For Each Sht In Arr
        Sheets(Sht).Delete
    Next Sht
Application.DisplayAlerts = True


End Sub

When I debug the code the error is happening:

Sheets(Sht).Delete

Any ideas as to what is causing the problem or any suggestions on how to fix?

Thanks for you help!

 

[gallen]

Could be a number of things.

Ensure the spelling of each item in the array is correct.

Also make sure it isn't trying to delete the last sheet. There must always be at least 1 sheet in the workbook. FInally ensure the sheet isn't protected in anyway...

 

[Fluff]

The VBA deletes all the sheets in a workbook that are not listed

Your code actually deletes the sheets that ARE listed, rather than those that aren't.
Your code works for me & I cannot get the same error that you receive.
Does it delete any of the sheets, or does it fail on sheet Summary?

 

[bbalch]

Fluff, this is actually some code you put together for me a few months back and has worked perfectly since then but just stopped today for some reason. I have two bits of code saved in one module, one deletes the sheets listed the other deletes sheets not listed. I think the code above is the one the deletes the listed sheets. This code deletes those that are listed... I think I can't test because neither are working.

Sub DelSheetsv2()

    Dim Arr As Variant
    Dim Sht As Worksheet
    
    Arr = Array("SUMMARY", "General", "INDUSTRY INITIAT", "SPECIAL PROJECTS", "CHRISTMAS SPEC", "AWARDS SHOW", "Music Fest")
    
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


End Sub

Gallen, The sheets are named correctly, there are over 100 sheets to begin with and the vba should trim it down to 7 sheets, and the workbook is not protected in anyway.

I'm not sure if it matters or not but I'm running Excel 2016.

 

[Fluff]

The code in your op deletes the sheets that are in the array, whilst the code in post#4 deletes the sheets that are not in the array.
Both work for me & still cannot generate the error that you are getting. The closest I get is with workbook protection on.

 

[Norie]

Do you have more than workbook open when you get the error?

 

[bbalch]

It happens whether I have one workbook open or multiple. What's interesting is that I had a co-worker run the macro on their machine and it worked properly.

 

[Fluff]

Is the code located in the workbook it's working on?
If so check your personal.xlsb & make sure you don't have any subs called Sht or Delete.
Also make sure you haven't any Module or global declarations (ie a dim statement at the top of a module before any code)

 

[bbalch]

The code is located in my personal.xlsb workbook. I looked through my subs and don't have any named Sht or Delete.

I've started to receive an error message when opening excel "File in Use. PERSONAL.XLSB is locked for editing. Open 'Read-Only' or click 'Notify' to open read-only and receive notification when the document is no longer in use"

Do you think this has something to do with the issues I'm having?