PDA

View Full Version : Delete all worksheets but exclude ?



Shazam
03-08-2006, 07:48 AM
I can't get this code to work. I would like to delete all worksheets in the workbook except the ones I specify in the Array of the code.



Sub Do_Not_Delete_these_Sheets()
Application.ScreenUpdating = False
Dim mySheets, i
mySheets <> (Array("1st Shift C", "2nd Shift C", "1st Shift Clean", "2nd Shift Clean"))
For i = LBound(mySheets) To UBound(mySheets)
Worksheets(mySheets(i)).Activate
ActiveSheet.Delete
Next
Application.ScreenUpdating = True
End Sub

matthewspatrick
03-08-2006, 08:56 AM
How about:


Sub KillThem()

Dim ws As Worksheet

Application.DisplayAlerts = False

For Each ws In ActiveWorkbook.Worksheets
Select Case UCase(ws.Name)
Case "EXCLUDE1", "EXCLUDE2", "EXCLUDE3"
'do nothing
Case Else
ws.Delete
End Select
Next

Application.DisplayAlerts = True

End Sub

malik641
03-08-2006, 08:59 AM
Or....using your original code:


Option Explicit
Sub Do_Delete_these_Sheets()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim mySheets, i
mySheets = Array("1st Shift C", "2nd Shift C", "1st Shift Clean", "2nd Shift Clean")
For i = LBound(mySheets) To UBound(mySheets)
Worksheets(mySheets(i)).Delete
'ActiveSheet.Delete
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub



Not sure why you set the mysheets "<>"????

Norie
03-08-2006, 09:49 AM
malik

I think the OP didn't want to delete the sheets in the array.:)

malik641
03-08-2006, 10:10 AM
malik

I think the OP didn't want to delete the sheets in the array.:)
Hmm....I was just confused by the code...my bad.

Well, if that's the case, I apologize and present:


Option Explicit
Sub Do_Delete_these_Sheets()
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim mySheets, i
Dim ws As Worksheet
Dim arrWS As String
mySheets = Array("1st Shift C", "2nd Shift C", "1st Shift Clean", "2nd Shift Clean")
For Each ws In ActiveWorkbook.Worksheets
For i = LBound(mySheets) To UBound(mySheets)
arrWS = mySheets(i)
If ws.Name <> Worksheets(arrWS) Then ws.Delete
Next i
Next ws
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub


Thanks Norie :)

Shazam
03-08-2006, 05:54 PM
Thanks for replying. I tried both of your codes its deleting all the worksheets. It should only delete worksheets "Chart" & "Bar". Any Ideas?

I put the sample workbook below with both codes attach to the workbook.

matthewspatrick
03-08-2006, 05:57 PM
Shazam,

Convert all the worksheet names in the first Case line to upper case :)

The reason I did this is because, by default, in Select Case the text comparisons are case-sensitive, so I force the comparison to be against upper case.

Shazam
03-08-2006, 06:04 PM
I changed it in the code like this:



Case "1ST SHIFT C", "2ND SHIFT C", "1ST SHIFT CLEAN", "2ND SHIFT CLEAN"



But nonthing happends?

Shazam
03-08-2006, 06:08 PM
I just re-read your post. Can the code be modified? So it does not have to be case sensitive. Because I import these worksheets from at least 7 user's using a code.

XLGibbs
03-08-2006, 07:43 PM
Shazam,

I think you can just change

If ws.Name <> Worksheets(arrWS) then ws.Delete

to


If UCase(ws.Name) <> Worksheets(UCase(arrWS)) then ws.Delete

Which will make it irrelevant as to the case in the array or in the ws.name itself by converting both to uppercase in the evaluation

Shazam
03-08-2006, 08:36 PM
Thanks for all your help everyone. Each and evey one of you came me an idea. You guys are great!!:friends:






Sub Do_Not_Delete_These_Sheets()
Dim sh As Worksheet
Application.DisplayAlerts = False
Nme1 = UCase(["1st Shift C"])
Nme2 = UCase(["2nd Shift C"])
Nme3 = UCase(["1st Shift Clean"])
Nme4 = UCase(["2nd Shift Clean"])
For Each sh In ThisWorkbook.Worksheets
Select Case UCase(sh.Name)
Case Nme1, Nme2, Nme3, Nme4
'Do Nothing
Case Else
sh.Delete
End Select
Next sh
Application.DisplayAlerts = True
End Sub

matthewspatrick
03-08-2006, 09:16 PM
Shazam,

Just to be sure, did you want to delete 1st Shift C, 2nd Shift C, 1st Shift Clean, and 2nd Shift Clean, or did you want to delete everything except those worksheets?

Shazam
03-08-2006, 09:29 PM
I would like to delete everything except those. One more thing I have a worksheet name "Performance 03-01-2006" and its daily.All I do is just change the date. How can it be modified not to delete the worksheet thats start with Performace?



Sub Do_Not_Delete_These_Sheets()
Dim sh As Worksheet
Application.DisplayAlerts = False
Nme1 = UCase(["1st Shift C"])
Nme2 = UCase(["2nd Shift C"])
Nme3 = UCase(["1st Shift Clean"])
Nme4 = UCase(["Performance"*])
For Each sh In ThisWorkbook.Worksheets
Select Case UCase(sh.Name)
Case Nme1, Nme2, Nme3, Nme4
'Do Nothing
Case Else
sh.Delete
End Select
Next sh
Application.DisplayAlerts = True
End Sub


XLGibbs supply me with this code but i just can't get it to work with the code above.




For Each ws In ActiveWorkbook.Worksheets
If Left(ws.Name, 10) = "Performace" Then
ws.Move Before:=Sheets(1)
End If
Next ws

matthewspatrick
03-08-2006, 11:08 PM
Shazam,

This works:


Sub KillThem()

Dim ws As Worksheet

Application.DisplayAlerts = False

For Each ws In ActiveWorkbook.Worksheets
Select Case UCase(ws.Name)
Case "1ST SHIFT C", "2ND SHIFT C", "1ST SHIFT CLEAN", "2ND SHIFT CLEAN"
Case Else
ws.Delete
End Select
Next

Application.DisplayAlerts = True

End Sub

malik641
03-09-2006, 06:14 AM
Try this:

Option Explicit
Sub Do_Not_Delete_These_Sheets()
Dim sh As Worksheet
Application.DisplayAlerts = False
Nme1 = UCase(["1st Shift C"])
Nme2 = UCase(["2nd Shift C"])
Nme3 = UCase(["1st Shift Clean"])
nme4 = UCase(["Performance"])
For Each sh In ThisWorkbook.Worksheets
Select Case UCase(sh.Name)
Case Nme1, Nme2, Nme3
'Do Nothing
Case Else
If Left(UCase(sh.Name), 11) <> nme4 Then sh.Delete
End Select
Next sh
Application.DisplayAlerts = True
End Sub

Shazam
03-09-2006, 07:06 AM
Thank You both. Both codes works perfectly.:friends:

mdmackillop
03-09-2006, 10:48 AM
Hi Shazam,
If you add an "Option Compare Text" statement at the head of your code, there is no need for Ucase
Regards
MD

Shazam
03-09-2006, 04:22 PM
Thanks mdmackillop I did not know that.





Option Explicit
Option Compare Text
Sub Do_Not_Delete_These_Sheets()
Dim sh As Worksheet
Application.DisplayAlerts = False
Nme1 = (["1st Shift C"])
Nme2 = (["2nd Shift C"])
Nme3 = (["1st Shift Clean"])
nme4 = (["Performance"])
For Each sh In ThisWorkbook.Worksheets
Select Case (sh.Name)
Case Nme1, Nme2, Nme3
'Do Nothing
Case Else
If Left((sh.Name), 11) <> nme4 Then sh.Delete
End Select
Next sh
Application.DisplayAlerts = True
End Sub

mdmackillop
03-10-2006, 01:39 AM
Hi Shazan,
You should still have Option Explicit (I've added it to your code), as they serve different purposes.
Regards
MD

matthewspatrick
03-10-2006, 07:21 AM
Shazam,

Expanding on Malcolm's post, there are four (I think) Option statements you can make as module-level declarations. See the VBA help for more info on them...


Option Explicit


This one forces you to declare all variables. It is a Very Good Idea to use it all the time. In fact, you should set up your VB Editor to default to this. From the VBE, select Tools|Options from the menu, and on the editor tab check require variable declaration. Now, when you insert a new module, the Editor will helpfully include the line by default.


Option Compare {Binary | Text | Database}


This controls how VBA will compare text strings. Essentially, Binary is the default for Excel VBA, and it is case-sensitive. Text is no case-sensitive. Database is the default in Access VBA, and it is only valid in Access.

Several VBA functions allow you to override this setting. For example, I try to remember to use StrComp to do string comparisons instead of the = operator; with StrComp, I get to decide what textual comparison setting I want to use for that operation.


Option Base {0 | 1}


By default, VBA sets the lower bound of an array at 0. If you want your arrays to start at 1 instead, use Option Base 1.

Note that certain functions and methods return arrays, and that this statement will have no bearing on the lower bound of such arrays!


Option Private Module


This statement means that the module's contents are visible within that VBA Project, but not elsewhere. So, for example, if you have a bunch of subs or functions in a workbook, but you do not want them to show up in the 'run macro' dialog box or in the list of UDFs in Excel, putting Option Private Module in will hide those items from the Excel UI.

Shazam
03-10-2006, 07:27 AM
Hi matthewspatrick,


Thanks for that overview. I'm very intrigue. I will print it out the thread and re-read it over the weekend.

matthewspatrick
03-10-2006, 07:34 AM
Amplifying a point here...




Option Base {0 | 1}


By default, VBA sets the lower bound of an array at 0. If you want your arrays to start at 1 instead, use Option Base 1.

Note that certain functions and methods return arrays, and that this statement will have no bearing on the lower bound of such arrays!


To elaborate a bit here...


Option Explicit

Public MyArray(10) As Long


In the case above, MyArray will have a lower bound of 0, and thus have 11 elements (0 through 10). If I wanted the lower bound to be 1, I could have used:


Option Explicit

Public MyArray(1 To 10) As Long


Now consider:


Option Explicit
Option Base 1

Public MyArray(10) As Long


Now, the array will have a lower bound of 1, and have 10 elements in all. If I wanted the default array behavior to be starting at 1, but for this array to start at 0:


Option Explicit
Option Base 1

Public MyArray(0 To 10) As Long


Generally speaking, it is always a good idea to explicitly dimension your arrays as "X To Y"; that way, you never have to worry about whether or not Option Base is being used.

Patrick

Shazam
03-10-2006, 07:44 AM
Me again is this possible. I would like to list the worksheets on sheet1 in the workbook that ones I dont want to delete. I think that route will be easier instead of me adding worksheets names to the code below. What do you think?



Option Explicit
Option Compare Text
Sub Do_Not_Delete_These_Sheets()
Dim sh As Worksheet
Application.DisplayAlerts = False
Nme1 = (["1st Shift C"])
Nme2 = (["2nd Shift C"])
Nme3 = (["1st Shift Clean"])
nme4 = (["Performance"])
For Each sh In ThisWorkbook.Worksheets
Select Case (sh.Name)
Case Nme1, Nme2, Nme3
'Do Nothing
Case Else
If Left((sh.Name), 11) <> nme4 Then sh.Delete
End Select
Next sh
Application.DisplayAlerts = True
End Sub