Macro: Delete Specified Sheets

xamil · Dec 1st 2006

I need to have a vba macro code to delete certain worksheets with names such as sheet1, sheet2, ... sheetn. , so same name with different suffix. I want to delete these sheets only if they exist in the workbook. That is, the logic is something like this :
if sheetname="Sheet1" or "Sheet2" or, ... "Sheetn" then delete.

Could anyone help me with this macro code?

Many thanks in davance !!

tinyjack · Dec 1st 2006

Re: a vba macro code to delete certain worksheets

You could use a For Each ... Next loop:

Code

Dim wsItem As Worksheet


For Each wsItem In ThisWorkbook.Worksheets
    'Change delete to whatever you need
    'DisplayAlerts can be used to avoid dialog box being displayed
    If wsItem.Name Like "delete*" Then wsItem.Delete
Next

HTH

TJ

Dave Hawley · Dec 2nd 2006

Re: a vba macro code to delete certain worksheets

Another way

Code

Sub DeleteSomeSheets()
Dim sHeet As Object


    Application.DisplayAlerts = False
        For Each sHeet In Sheets
            Select Case UCase(sHeet.Name)
                Case "SHEET1", "SHEET2", "SHEET4", "SHEET6"
                    sHeet.Delete
                Case Else
                  'Whatever
            End Select
        Next sHeet
    Application.DisplayAlerts = True
End Sub

Display More

Macro: Delete Specified Sheets

Participate now!

Share