Array Code to Delete Worksheets

[Volsfan]

I am using some code to create arrays based on the value of a cell. Works
just great. Now I need to figure out how to use the same value to create case
values that can make array's to delete some different cells. As an simple
example:

Cell a1 is A or b or c etc

Code sets up arrays to display only certain sheets ie

a = (sheet1,sheet4,sheet5)
b=(sheet22,sheet23,sheet24)

For the same a or b, i want to delete some sheets (but not all others) as
well.

Can anyone please make a suggestion on this.

Thanks in advance.

 

[Norman Jones]

Hi Volsfan,

If I understand correctly, perhaps something like the following may help:

'======================>>
Public Sub Tester02()
Dim SH As Worksheet
Dim arrDelete As Variant
Dim arrExceptions As Variant

arrDelete = Array("Sheet1", "Sheet4", "Sheet5", "Sheet7", "Sheet9")
arrExceptions = Array("Sheet4", "Sheet7")

For Each SH In ActiveWorkbook.Sheets
If Not IsError(Application.Match(SH.Name, arrDelete, 0)) Then
If IsError(Application.Match(SH.Name, arrExceptions, 0)) Then
'Do something, e.g.:
MsgBox SH.Name
'Or
'SH.Delete
End If
End If
Next SH

End Sub
'======================>>

 

[Volsfan]

Thanks Norman,

I am traveling, but I will try when I return. I see some things in there
that look promising.

 

[Volsfan]

Ok, Here is the code that I am using to determine which sheets are visible
and which are not:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim arySheets
Dim nVisible As Long
Dim i As Long
Dim sh As Worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$H$40" Then
With Target
Select Case .Value
Case "A": arySheets = Array("INPUT30", "LD3-30", "WB30",
"CREWWB30", "TABLES30", "LOADSHEET30", "OFFLOAD30", "FPS-CPM30")
Case "B": arySheets = Array("INPUT29", "LD3-29", "WB29",
"CREWWB29", "TABLES29", "LOADSHEET29", "OFFLOAD29", "FPS-CPM29")
Case "T": arySheets = Array("START", "INPUT29", "INPUT30",
"WB29", "WB30", "LD3-29", "LD3-30", "CREWWB29", "CREWWB30", "LOADSHEET29",
"LOADSHEET30", "CGCALCS29", "CGCALCS30", "TABLES", "TABLES29", "TABLES30",
"LDF29", "LDF30", "FPS-CPM29", "FPS-CPM30", "OFFLOAD29", "OFFLOAD30")
End Select
End With
End If

For Each sh In ThisWorkbook.Worksheets
nVisible = xlSheetHidden
For i = LBound(arySheets) To UBound(arySheets)
If sh.Name = arySheets(i) Then
nVisible = xlSheetVisible
Exit For
End If
Next i
sh.Visible = nVisible
Next sh

ws_exit:
Application.EnableEvents = True

End Sub

It works great (Thanks Tom Ogilvy)

I am trying to add code to this change event that will delete the other
sheets that I do not display. Keep in mind that there are other sheets that
do not show up in the above arrays that feed data, but are hidden. So I
cannot just delete all the others except the ones above. Also, I have some
other sheets that have as many as 10 cases vs the 2 shown here. I am trying
to use something like this below, but am not having much luck.

Dim arrDelete As Variant
Dim i as Long
Dim sh as Worksheets

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$H$40" Then
With Target
Select Case .Value
Case "A": arrDelete = Array("INPUT29", "LD3-29", "WB29",
"CREWWB29", "TABLES29", "LOADSHEET29", "OFFLOAD29", "FPS-CPM29")
Case "B": arrDelete = Array("INPUT30", "LD3-30", "WB30",
"CREWWB30", "TABLES30", "LOADSHEET30", "OFFLOAD30", "FPS-CPM30")
Case "T": arrDelete = Array("OFFLOAD29")
End Select
End With
End If

For Each sh In ThisWorkbook.Worksheets
For i = LBound(arrDelete) To UBound(arrDelete)
If sh.Name = arrDelete(i) Then
Worksheets(Array(i)).Delete
Exit For
End If
Next i
Next sh

ws_exit:
Application.EnableEvents = True

Both sections of code do have the arrays spread out, this forum just sliced
them up so that is not the issue.

Can someone help me either integrate this code with the previous, or suggest
a way to add these delete statements to a control button, menu item, or
something?

Thanks again for all the help.