How to delete all sheets except specified/current one in Excel?
For deleting all sheets in an Excel workbook except specified one or current active sheet, you can deal with this as below methods shown.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
With below VBA code, you can delete all sheet in a workbook except specific one in Excel.
1. Press Alt + F11 keys simultaneously to open the Microsoft Visual Basic Application window.
2. Click Insert > Module, then copy and paste below VBA code into the Module window.
VBA code: Delete all sheets except specified ones
Sub DeleteSheets1() Dim xWs As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False For Each xWs In Application.ActiveWorkbook.Worksheets If xWs.Name <> "Sheet1" And xWs.Name <> "Sheet2" Then xWs.Delete End If Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
1. “Sheet1” and “Sheet2” are sheets you want to keep in current workbook. You can change them based on your needs.
2. If you want to delete all but keep more sheets, please add code And xWs.Name <> "your sheet name" before the Then word in the sixth line of the code. Such as:
If xWs.Name <> "Sheet1" And xWs.Name <> "Sheet2" And xWs.Name <> "your sheet name" Then
3. If you just want to keep one sheet and delete all from the workbook, please remove all And condition in the code. For example, for keeping sheet1 in current workbook, you need to change the sixth line code to: If xWs.Name <> "Sheet1" Then
You can easily remove all sheets except current active sheet with just one click by Delete All Inactive Sheets utility of Kutools for Excel.
1. Click Enterprise > Worksheet > Delete All Inactive Sheets. See screenshot:
3. Then a dialog box pops up, if you determine to delete all sheets except active one, click the OK button, otherwise, click the Cancel button.
Then all sheets are deleted except the current active one in your workbook.
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!