VBA code, delete worksheets

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

Hello,

Could someone advice on the VBA statement that allows me to delete all
worksheets within my workbook except for Sheet1 and Sheet2.

Example: Mij Workbook contains (for example) the following worksheets:
Sheet1, Sheet2,Sheet3,Sheet4, Sheet5
Before further steps are taken I would like to delete all sheets
except Sheet1 and Sheet2. As the deleted sheets might vary in name and
number it is prefered to define a statement that deletes all sheets
except for Sheet1 and Sheet2. I tried to create a statement using the
recorder but in this occasion all actual names are recorded.

many thanks!!!!

Best regards,
Robert
 
Sub deleteshts()
Application.DisplayAlerts = False
For i = 3 To Sheets.Count
Sheets(i).Delete
Next
Application.DisplayAlerts = True
End Sub
 
A pop up will come up asking you to confirm that you really want to delete.
here is the code

Sub deletesheets()

For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Sheet1" And _
sh.Name <> "Sheet2" Then

sh.Delete
End If
Next sh

End Sub
 
Public Sub DeleteSheets()

Application.DisplayAlerts = False
For Each sh In ThisWorkbook.Sheets

If sh.Name <> "Sheet1" And _
sh.Name <> "Sheet2" Then

sh.Delete
End If
Next sh
Application.DisplayAlerts = True

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
For Each sh In ThisWorkbook.Sheets
Select Case sh.Name
Case "Sheet1", "Sheet2"
'Do nothing
Case Else
sh.Delete
End Select
Next sh
 
Back
Top