CODEWITHSUNDEEP
excelvba
DRod
DRod

Reputation: 21

Delete worksheets

I'm trying to run code that deletes all worksheets bar three.

The code below results in a syntax error: 

Sub Delete_Sheets()
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Sheet1", "LBO", "Factset codes" Then ws.Delete
Next
Application.DisplayAlerts = True
End Sub

Upvotes: 2

Views: 133

Answers (3)

brettdj
brettdj

Reputation: 55672

Rather than loop through all sheets and test for three names, you could more simply move the three sheets you want to a new book:

This can be done in a single line, you could add further code to close the original workbook, error handling to check all these sheets exist etc.

Sheets(Array("Sheet1", "Factset code", "LBO")).Copy

Upvotes: 1

PatricK
PatricK

Reputation: 6433

You cannot put all "Names to check" in one line like that. Try this (replace the If block of yours):

Select Case ws.Name 
    Case "Sheet1", "LBO", "Factset codes"' Do Nothing here
    Case Else: ws.Delete
End Select

EDIT (Add code to make it silent delete):

Application.DisplayAlerts = False
Select Case ws.Name
    Case "Sheet1", "LBO", "Factset codes" ' Do Nothing here
    Case Else
        If Worksheets.Count > 1 Then ws.Delete
End Select
Application.DisplayAlerts = True

Upvotes: 3

SilentRevolution
SilentRevolution

Reputation: 1513

While @PatricK's answer is the most elegant, the problem with your code is the if statement, if you have multiple criteria, you have to write something along the lines of:

If Not ws.Name = "Sheet1" And Not ws.Name = "LBO" And Not ws.Name = "Factset codes" Then
    ws.Delete
End If

On a side note if you write the code to execute if logic test is true on the same line as the logic test like so:

If Not ws.Name = "Sheet1" And Not ws.Name = "LBO" And Not ws.Name = "Factset codes" Then ws.Delete

you don't need the End If statement.

Upvotes: 0

Related Questions