Delete Sheets Problem

[Guest]

Hi

Why isnt this code working? It is coming up with the 'Type Mismatch' error.

Thanks

Sub DeleteSheets()

For x = 1 To Worksheets.Count
If Worksheets(x).Name <> "Menu" Or "Sage Export" Then
Worksheets(x).Delete
End If
Next x

End Sub

 

[Guest]

Hi Edgar,

Try this. It worked for me.

Sub DeleteSheets()

For x = 1 To Worksheets.Count
If Worksheets(x).Name <> "Menu" Or Worksheets(x).Name <> "Sage Export"
Then
Worksheets(x).Delete
End If
Next x

End Sub

 

[Bob Phillips]

Edgar,

Another approach

Sub Macro1()
DeleteSheet "Menu"
DeleteSheet "Sage Export"
End Sub

'-----------------------------------------------------------------
Function DeleteSheet(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Object
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
Application.DisplayAlerts = False
wb.Sheets(Sh).Delete
Application.DisplayAlerts = True
On Error GoTo 0
End Function

 

[Tom Ogilvy]

Seems like every sheet would meet that test. Sage Export <> Menu or Menu
<> Sage Export. I think you would need (besides the problem of the number
of sheets changing as each is deleted).

Sub DeleteSheets()
Dim j as Long, x as Long
j = 1
For x = 1 To Worksheets.Count
If Worksheets(j).Name <> "Menu" And _
Worksheets(j).Name <> "Sage Export" Then
Application.DisplayAlerts = False
Worksheets(j).Delete
Application.DisplayAlerts = True
Else
j = j + 1
End If
Next x
End Sub

to exclude Menu and Sage Export.

I actually ran this and it really did work for me.