PDA

View Full Version : Solved: delete all sheets not in array(1 to 4)



mperrah
08-10-2007, 06:44 PM
I have a workbook with 10 sheets.
I generate a report that in order to shrink the file size for email
I delete the sheets not needed for the report.
The sheet names I keep are always the same, but the ones I want to delete will change every time.

I have this code that deletes specific sheets,
how can I modify it to delete all but these?


Dim sht, sarray

sarray = Array("QCDetail", "WQC", "Chart", "WPR")
For Each sht In sarray
Worksheets(sht).Delete
Next sht

This code was from Rory, I believe...
Any help is welcome.
Mark

YellowLabPro
08-10-2007, 07:34 PM
Not Tested:

Dim sht, sarray

sarray = Array("QCDetail", "WQC", "Chart", "WPR")
For Each sht Not In sarray
Worksheets(sht).Delete
Next sht

mperrah
08-10-2007, 09:37 PM
Last code shows red (error) in editor

Dim sht, sarray

sarray = Array("QCDetail", "WQC", "Chart", "WPR")
For Each sht Not In sarray ' this line errors
Worksheets(sht).Delete
Next sht

I found this code but errors on sheet.delete

For Each Sheet In Sheets
If Not (Sheet.Name = "QCDetail" _
Or Sheet.Name = "WPR" _
Or Sheet.Name = "Chart" _
Or Sheet.Name = "WQC") Then
Sheet.Delete ' this line errors
End If
Next

Bob Phillips
08-11-2007, 02:21 AM
Dim sht, sarray

sarray = Array("QCDetail", "WQC", "Chart", "WPR", "Sheet1", "Sheet2")
Application.DisplayAlerts = False
For Each sht In ActiveWorkbook.Worksheets
If Not IsError(Application.Match(sht.Name, sarray, 0)) Then
sht.Delete
End If
Next sht
Application.DisplayAlerts = True

Bob Phillips
08-11-2007, 02:21 AM
Dim sht, sarray

sarray = Array("QCDetail", "WQC", "Chart", "WPR")
Application.DisplayAlerts = False
For Each sht In ActiveWorkbook.Worksheets
If Not IsError(Application.Match(sht.Name, sarray, 0)) Then
sht.Delete
End If
Next sht
Application.DisplayAlerts = True

mperrah
08-11-2007, 02:56 PM
this just deleted the sheets in the array,
I was tring to delete the sheets not in the array,
I have the name of the sheets I'm keeping and the ones I delete may have different names each time, so naming the ones to keep and delete all else is the goal.
Thank you again for your help.
Mark

Bob Phillips
08-11-2007, 03:24 PM
Then just take out the Not



Dim sht, sarray

sarray = Array("QCDetail", "WQC", "Chart", "WPR")
Application.DisplayAlerts = False
For Each sht In ActiveWorkbook.Worksheets
If IsError(Application.Match(sht.Name, sarray, 0)) Then
sht.Delete
End If
Next sht
Application.DisplayAlerts = True

mdmackillop
08-11-2007, 03:57 PM
Nihil simul inventum est et perfectum
So true!

Bob Phillips
08-12-2007, 01:33 AM
So true!

This thread is testimony <G>

Bob Phillips
08-12-2007, 01:34 AM
I take it that you mean that this thread is testimony <G>

mperrah
08-12-2007, 08:02 PM
This last version gives a class error on the sht.Delete line
runtime error 1004
delete method of worksheet class failed

I've been reading different options and have this so far, still not working.
I'm having problems with objects, declaring properly?

Select Case Worksheet.name 'problem here
Case Worksheet(QCDetail)
Case Worksheet(WQC)
Case Worksheet(Chart)
Case Worksheet(WPR)
Case Worksheet(MenuSheet)

Case Else
Worksheet.Delete
End Select
or
sarray = Array("QCDetail", "WQC", "Chart", "WPR", "MenuSheet")
For Each sht In ActiveWorkbook.Worksheets
If sht.Name <> sarray Then ' object type mismatch?
' I think I am going from name of sheet to the actual sheet in the array
' if I delete the name does the sheet persist?
' or deleting name deletes sheet too?
sht.Delete
End If
Next sht

YellowLabPro
08-12-2007, 08:07 PM
Try enclosing your worksheet names in quotes.
This is a poke in the dark, but unless these are qualified as variables they need to recognized by VBA as text.

Case Worksheet("QCDetail")
Case Worksheet("WQC")
Case Worksheet("Chart")
Case Worksheet("WPR")
Case Worksheet("MenuSheet")

Ken Puls
08-12-2007, 09:31 PM
Hi Mark,

If you want to use a Case statement, try this:
Dim sht As Worksheet
Application.DisplayAlerts = False
For Each sht In ActiveWorkbook.Worksheets
Select Case sht.Name
Case Is = "QCDetail", "WQC", "Chart", "WPR"
'Do nothing
Case Else
sht.Delete
End Select
Next sht
Application.DisplayAlerts = True

I'm not sure I follow why you aren't using Bob's latest submission though. It seemed to work for me...

mperrah
08-12-2007, 09:59 PM
His and yours fail in my workbook
method 'delete' of object '_worksheet' failed
is the error message,
I'm not sure but I think it is how the variables are dim 'ed
both codes give he same error

it's on the sht.delete line

I tried dim sht (there rest blank)
and it gives a class error on the same line

Ken Puls
08-12-2007, 10:01 PM
Does your workbook have at least one sheet that matches the names given? It will be case sensitive as well...

Ken Puls
08-12-2007, 10:04 PM
This removes the case sensitivity portion, and will also let you know if it is trying to delete the last sheet in the workbook:

Dim sht As Worksheet
Application.DisplayAlerts = False
For Each sht In ActiveWorkbook.Worksheets
Select Case UCase(sht.Name)
Case Is = "QCDETAIL", "WQC", "CHART", "WPR"
'Do nothing
Case Else
If Not ActiveWorkbook.Worksheets.Count = 1 Then
sht.Delete
Else
msgbox "Last worksheet could not be deleted!"
End If
End Select
Next sht
Application.DisplayAlerts = True

mperrah
08-12-2007, 10:05 PM
I had one short at first but I added in all the names for the first case
then additional named sheets to test the delete, still error on method delete of object worksheet

mperrah
08-12-2007, 10:13 PM
Here is the whole sub. Maybe my problem is somewhere else...

Sub SaveForWPR()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim sFileName As String
Dim ws As Worksheet

sFileName = Application.GetSaveAsFilename(InitialFileName:=Range("wprName"))

On Error Resume Next
Set ws = Sheets("Raw")
If ws Is Nothing Then
MsgBox "This task must be performed from Full version"
Exit Sub
End If

On Error GoTo 0
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
Select Case UCase(sht.Name)
Case Is = "QCDETAIL", "WQC", "CHART", "WPR", "MENUSHEET"
'Do nothing
Case Else
If Not ActiveWorkbook.Worksheets.Count = 1 Then
sht.Delete
Else
MsgBox "Last worksheet could not be deleted!"
End If
End Select
Next sht

If sFileName = "False" Then

End If

ThisWorkbook.SaveAs sFileName

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Ken Puls
08-12-2007, 10:41 PM
Did you protect the workbook structure, by any chance?

mperrah
08-12-2007, 11:18 PM
No protection enabled
Is the problem in the case scenario?
we determin the sheet names that are not in the list in one case
the other just says delete sheet if no error,
does that explicitly say delete the sheet if it's name is anything but the list.

how could we build an array of sheets (or sheet names) that we want to delete
and compare the array of what we want to keep,
and delete the difference.
My trouble is coding for a name that will be different every time (to delete)
the save names are constant.
I thought the case scenario would work,
or make an array and use: if name <> array then delete, but I couldn't get that to work...

Bob Phillips
08-13-2007, 12:44 AM
It worked fine for me, up to the final SaveAs where it tries to use sFilen ame that had no value.

mperrah
08-13-2007, 12:51 AM
Here is my file so you have all the sheets.
The sub we are working in is in modsave
I have excel 2007 the file is 97-2003 compatible mode, if that helps.

Bob Phillips
08-13-2007, 01:22 AM
I can't run this. I get a diuplicate declaration error on Dim wsht, and when I step through it it keeps dropping into FileSavedln when deleting the sheet. Either you are doing something odd that I am not seeing, or that code is well and truly up the swannee.

mperrah
08-13-2007, 02:38 AM
This just stops at sht.delete
I tried Worksheets(sht).delete 'still breaks
I'm not sure what is failing.
I have several procedures that have a sht dim on the same module
but not more than one in the same sub.
I could change the variable used for each if you think that might help?
the error is the same: method 'delete' of object '_worksheet' failed

Not sure where to check
:think:

YellowLabPro
08-13-2007, 03:33 AM
Mark,
This works:Option Explicit

Sub deletesht()

Dim sht, sarray
sarray = Array("QCDetail", "WQC", "Chart", "WPR")
Application.DisplayAlerts = False
For Each sht In ActiveWorkbook.Worksheets
On Error Resume Next
If IsError(Application.Match(sht.Name, sarray, 0)) Then
sht.Delete
End If
On Error GoTo 0
Next sht
Application.DisplayAlerts = True
End Sub

I created a test environment w/ a new workbook, created the names of the sheets you want to keep in the workbook, and some others, and then ran "xld's" code.
That worked, then I ran the code on your book, that did what you wanted, but did break on the line sht.Delete.
My guess, after searching through your code, which after a few minutes I gave up(I looked in the protect module and commented that out, but there may be another call in there somewhere), is that there is some conflict between the delete method and one of your other module's code that it is looking for, or do something or a call and is failing.
So a simple on error resume statement fixes that and leaves the four books you want.
If this is not what you want, at least it is a starting point for you to find the offending code.
This worked.

daniel_d_n_r
08-13-2007, 04:09 AM
This works too ..

Sub Del_Sheets()
Dim ws As Worksheet
Dim ms As Variant
Dim x As Boolean
ms = Array("QCDETAIL", "WQC", "CHART", "WPR", "MENUSHEET")
For Each ws In ThisWorkbook.Worksheets
x = 0
s = ws.Name
For Each i In ms
If InStr(1, CStr(s), CStr(i), vbTextCompare) Then
x = 1
End If
Next
If x = 0 Then
Application.DisplayAlerts = False
Sheets(s).Delete
Application.DisplayAlerts = True
End If
Next
ThisWorkbook.Save
End Sub


Cheers

YellowLabPro
08-13-2007, 04:26 AM
Hello Daniel,
Did you test on mperrah's workbook? When I test your code on his book, the same error pops up. Also it does not delete the "Menusheet" worksheet.

Also, these variables need declaring:
Dim s As String
Dim i As Variant

Mperrah,
I did test for deleting a Chart sheet in my test condition, but maybe there is someplace you can check if you wish to find the actual error cause.

Good luck

daniel_d_n_r
08-13-2007, 04:37 AM
The code will delete every sheet in the workbook except the ones declared in the array, those ones will stay.
I didnt bother with declaring every variable,,if so you could add
Option explicit

hmmm,, if it didn't run in the workbook :dunno ..im not sure
are the VB libraries all referenced?

I guess i will have a look at the workbook.

the code work better like this with the workbookSub Del_Sheets()
Dim ws As Worksheet
Dim ms As Variant
Dim x As Boolean
ms = Array("QCDetail", "WQC", "Chart", "WPR", "MenuSheet", "Prompt")
For Each ws In ThisWorkbook.Worksheets
x = 0
s = ws.Name
For Each i In ms
If InStr(1, CStr(s), CStr(i), vbTextCompare) Then
x = 1
End If
Next
If x = 0 Then
Application.DisplayAlerts = False
Sheets(s).Delete
Application.DisplayAlerts = False
End If
Next
ThisWorkbook.Save
End Sub

However there is a variable named "prompt" appearing on the last run
this is causing the error
The sheet "prompt" appears in the project explorer but not in the sheets?

there also seems to be a sub that hides sheet "prompt",,its hidden and therefore cant be deleted,hence the error,,,,how to fix this would be to add this sheets name to the list.
it seems to have removed the error.

cheers

YellowLabPro
08-13-2007, 06:13 AM
Daniel,
Mis-read the last paragraph, sorry about that. I will go have a look.


I am not an expert here, but both of xld's and your's breaks at the same point on mperrah's workbook, the sheets get deleted fine but there is an error that halts the sub from ending.
Once you test it on his book it might become evident. I did not search too long for the culprit.
It was an oversight on my part, MenuSheet was in your array, not in mperrah's, so I did was not expecting to see this additonal sheet show up.

Ken Puls
08-13-2007, 08:26 AM
there also seems to be a sub that hides sheet "prompt",,its hidden and therefore cant be deleted,hence the error,,,,how to fix this would be to add this sheets name to the list.
it seems to have removed the error.

Good catch. A hidden sheet can be deleted, but one that is veryhidden will cause an error. :)

mperrah
08-13-2007, 05:35 PM
I apologize to everyone.
I have a lot of users at differnt levels of skill.
I have a prompt sheet to tell them how to enable macros and keeps the sheets hidden till the file opens with macros enabled.
This sheet was not on my save list.
Since I always have macros enabled that sheet was last thing on my mind.
the thisworkbook has the on open and on close commands
on close it hides all sheets so without macros they stay hidden.
and on open (if macros enabled) all sheets get revealed, and "prompt" gets veryhidden.
I'll revise the code to not delete prompt and see what happens.
Sorry for so much trouble.
At least all who follow this thread can learn in fewer steps then me.
Thank you again.
XLD, Ken, and YellowLabPro, my deepest gratitude

BTW: All systems go, tested and working :thumb

Mark

YellowLabPro
08-14-2007, 03:16 AM
mperrah,
Congrats on solving. I too get so involved and wrapped up I miss some of the particulars of the project and get sent into a tailspin. Don't forget Daniel- he found the veryhidden sheet.

Cheers

mperrah
08-14-2007, 12:28 PM
can't forget Daniel, Thank you
I went with the case select just because i haven't tried it before
Here is the working procedure.

Sub SaveForWPR()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim sFileName As String
Dim ws As Worksheet
Dim sht As Worksheet

sFileName = Application.GetSaveAsFilename(InitialFileName:=Range("wprName"))

On Error Resume Next
Set ws = Sheets("Raw")
If ws Is Nothing Then
MsgBox "This task must be performed from Full version"
Exit Sub
End If

On Error GoTo 0
For Each sht In ActiveWorkbook.Worksheets
Select Case UCase(sht.Name)
Case Is = "QCDETAIL", "WQC", "CHART", "WPR", "MENUSHEET", "PROMPT"
'Do nothing
Case Else
If Not ActiveWorkbook.Worksheets.Count = 1 Then
sht.Delete
Else
MsgBox "Last worksheet could not be deleted!"
End If
End Select
Next sht

If sFileName = "False" Then

End If

ThisWorkbook.SaveAs sFileName

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub


This is what caused all the problems thanks to Daniels debugging we found it. Like I said, I always keep Macros enabled so I never see the prompt...
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call DeleteMenu

With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False

Call HideSheets

.ScreenUpdating = True
.EnableCancelKey = xlInterrupt
End With
End Sub

Private Sub HideSheets()
'
Dim Sheet As Object '< Includes worksheets and chartsheets
'
With Sheets("Prompt")
'
'the hiding of the sheets constitutes a change that generates
'an automatic "Save?" prompt, so IF the book has already
'been saved prior to this point, the next line and the lines
'relating to .[A100] below bypass the "Save?" dialog...
If ThisWorkbook.Saved = True Then .[A100] = "Saved"
'
.Visible = xlSheetVisible
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" _
And Not Sheet.Name = "MenuSheet" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next
'
If .[A100] = "Saved" Then
.[A100].ClearContents
ThisWorkbook.Save
End If
'
Set Sheet = Nothing
End With
'
End Sub

Thanks Again to All