Delete all sheets except sheet1 (VBA)

Seamus2626
Seamus2626 used Ask the Experts™
on
Hi, I need a line of code that deletes all sheets in the current workbook except the sheet called "Sheet1"

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Commented:
Try this:
Sub Demo()
    Dim sht As Sheet
    For Each sht in ActiveWorkbbok.Sheets
        If sht.Name < > "Sheet1" Then
            sht.Delete
        End If
    Next sht
End Sub

Open in new window

Ryan ChongSoftware Team Lead

Commented:
credits to MacroShadow, minor amendment to make it better:

Sub Demo(KeepWsh As String)
    Dim sht As Worksheet
    Application.DisplayAlerts = False
    For Each sht In ActiveWorkbook.Sheets
        If sht.Name <> KeepWsh Then
            sht.Delete
        End If
    Next sht
    Application.DisplayAlerts = True
End Sub

Open in new window

then:
Call Demo("Sheet1")

Open in new window

Seamus2626

Author

Commented:
Thanks!
Seamus2626

Author

Commented:
Thanks for the add Ryan :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial