Run-time error '1004':

bbalch

Board Regular
Joined
Feb 23, 2015
Messages
53
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"

Code:
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:
Code:
Sheets(Sht).Delete
Any ideas as to what is causing the problem or any suggestions on how to fix?

Thanks for you help!
 

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,931
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,910
Office Version
365
Platform
Windows
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

Board Regular
Joined
Feb 23, 2015
Messages
53
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.

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,910
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,361
Office Version
365
Platform
Windows
Do you have more than workbook open when you get the error?
 

bbalch

Board Regular
Joined
Feb 23, 2015
Messages
53
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,910
Office Version
365
Platform
Windows
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

Board Regular
Joined
Feb 23, 2015
Messages
53
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?
 

Forum statistics

Threads
1,081,418
Messages
5,358,559
Members
400,504
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top