I want to create a macro the deletes all worksheets within my workbook except
5. The worksheets I don't want deleted are named: Template, TY, LY, 52WkEnd,
and Macro. What code should I used to achieve this?

Hi,

ALT+F11 to open VB editor, right click 'ThisWorkbook' and insert module and
paste the code below in and run it

Sub selete_Sheets()
Dim ws As Worksheet
Dim DelFlag As Boolean
Dim MyArray As Variant
MyArray = Array("Template", "TY", "LY", "52WkEnd", "Macro")
For Each ws In ThisWorkbook.Worksheets
DelFlag = True
For x = LBound(MyArray) To UBound(MyArray)
If ws.Name = MyArray(x) Then
DelFlag = False
Exit For
End If
Next
If DelFlag Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
Next
End Sub

Mike

"CM4@FL" wrote:

I want to create a macro the deletes all worksheets within my workbook except
5. The worksheets I don't want deleted are named: Template, TY, LY, 52WkEnd,
and Macro. What code should I used to achieve this?

Press Alt + F11 to open the Visual Basic Editor and click INSERT on the menu
and select MODULE. You can paste the code below in to your module.

Sub RemoveSheets()

Dim wb As Workbook
Dim ws As Worksheet
Dim strWSName As String

Set wb = ThisWorkbook
'Suppress alerts and screen updates
With Application
.DisplayAlerts = False
.StatusBar = "Removing unwanted worksheets..."
.ScreenUpdating = False
End With
'Cycle through all sheets and remove all but the
'named sheets below
For Each ws In wb.Worksheets
strWSName = ws.Name
Select Case strWSName
Case "Template", "TY", "LY", "52WkEnd", "Macro"
Case Else
ws.Delete
End Select
Next ws
'Reset application screen and alerts settings
With Application
.DisplayAlerts = True
.StatusBar = False
.ScreenUpdating = True
End With

Set ws = Nothing
Set wb = Nothing

End Sub
--
Kevin Backmann


"CM4@FL" wrote:

I want to create a macro the deletes all worksheets within my workbook except
5. The worksheets I don't want deleted are named: Template, TY, LY, 52WkEnd,
and Macro. What code should I used to achieve this?