Delete Sheets

[lundbhaiz]

how do i delete more than 1 worksheet in a single macro ? i have many sheets to be deleted and i need some more code delete multiple sheets by their names.

here is my part of code which i use to delete 1 worksheet.

On Error Resume Next 
ThisWorkbook.Sheets("MySheet").Delete 
On Error Goto 0

 

[warhaug]

Just add more lines to your code.
This will delete more sheets on the same conditions.

Sub deletesheet()

On Error Resume Next
ThisWorkbook.Sheets("Sheet2").Delete 
ThisWorkbook.Sheets("Sheet3").Delete
ThisWorkbook.Sheets("Sheet4").Delete
On Error GoTo 0

End Sub

 

[eddiedj]

Hi,

Try:
Sheets(Array("Sheet6", "Sheet7", "Sheet8")).Select
Sheets("Sheet6").Activate
ActiveWindow.SelectedSheets.Delete

Tip: I recorded this macro when deleting multiple sheets

Eddie de Jong

 

[lundbhaiz]

nice! thanks you both of you. i knew warhaug type of answer but it was my last priority as i look for some code like defining an array.

 

[warhaug]

Following could be used to shorten macro.

Sheets(Array("Sheet6", "Sheet7", "Sheet8")).Delete

 

[Jonmo1]

Which list is shorter?
Sheets you want to delete
Sheets you DO NOT want to delete
?

If the list of sheets to NOT delete is shrter..

Dim ws As WorkSheet
For Each ws In WorkSheets
    Select Case ws.Name
        Case "This", "That", "Whatever" 'List of sheets you DO NOT want to delete
            'Do Nothing
       Case Else
            ws.Delete
    End Select
Next ws

If the list of sheets you WANT to delete is shorter, then

Dim ws As WorkSheet
For Each ws In WorkSheets
    Select Case ws.Name
        Case "This", "That", "Whatever" 'List of sheets to delete
            ws.Delete
        Case Else
            'Do Nothing
    End Select
Next ws