How to delete all sheets in Excel except one

Question

#2

Code:

Table of Contents Show

Download Practice Workbook
2 Simple Methods to Delete All Sheets Except One in Excel
1. Delete All Sheets Except One Manually
2. VBA Code to Delete All Sheets Except One in Excel
Related Articles
How do I delete multiple worksheets in Excel?
How do I delete multiple sheets in Excel using the keyboard?
How do I delete multiple unhide sheets in Excel?
How do you delete everything on sheets?

Sub test()
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
    If ws.Name <> "START" Then ws.Delete
Next ws
Application.DisplayAlerts = True
End Sub

#3

Something like

Code:

Sub deleteSheets()
Dim sh As Worksheet
Application.DisplayAlerts = False
For Each sh In Worksheets
    If sh.Name <> "START" Then sh.Delete
Next sh
Application.DisplayAlerts = True
End Sub

eliW

Well-known Member

#4

Michael - great !!!
Just what I needed and more!
I know now how to get rid of the annoying popup for confirming deletion
Thank you,

#5

My Pleasure, and thanks for the feedback....

#6

Code:

Sub test()
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
    If ws.Name <> "START" Then ws.Delete
Next ws
Application.DisplayAlerts = True
End Sub

Hey there
You could also change

Code:

 If ws.Name <> "START" Then ws.Delete

to

Code:

If ws.Name <> Activesheet.name Then ws.Delete

if you don't want a static sheet name.
Ants

#7

That will work IF your activesheet is the "START" sheet, otherwise the START sheet will be deleted along with all the other non activesheets

#8

Hey Guys
Sorry i meant to say if it was the sheet you currently had selected.
Ants

#9

Ants
Yeah, in that case you are correct.....but d'ya reckon you could trust your users to do that each time.....

#10

Michael,

Nope, can never trust users.

I guess you need to also take the whole macro into context as well. Another part may make the active sheet the one that you need to keep?

I only meant to show that its possible. I can't help much on these boards as im still learning heaps but i'll share what i can. And in future, i will re-read my posts to make sure i have everything in context.

Ants

While working in Microsoft Excel sometimes we need to delete all sheets from a workbook except one sheet or the active sheet. But doing it one by one is an irritating work. In that case, you can use some simple tricks to complete your task. Today in this article, I am sharing with you how to delete all sheets except one in Excel.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

2 Simple Methods to Delete All Sheets Except One in Excel

In the following, I have described 2 simple and easy methods to delete all sheets except one in Excel.

Suppose we have a workbook with multiple sheets- “Jan”, “Feb”, “Mar”, “Apr”, “May”, “Total Sales”. Now we will delete all sheets except the “Total Sales” sheet. Stay tuned!

1. Delete All Sheets Except One Manually

The simplest way to delete all sheets is by choosing them and using the sheet option. Follow the instructions below-

Steps:

While in a worksheet hold the CTRL key.

Next, choose your desired sheet. This will create a Group of worksheets. Now, we can perform similar operations to these sheets.
Click the right button of the mouse to get options from the Context Menu.
Then, press “Delete” from the options.

Finally, the selected all sheets will be deleted except one.

Read More: How to Delete Multiple Sheets in Excel (4 Ways)

Similar Readings

How to Delete Extra Pages in Excel (4 Methods)
Excel VBA: Delete Files with Wildcards (4 Methods)
How to Delete a Worksheet with No Prompt Using Excel VBA (5 Methods)
Excel VBA: Delete Sheet If It Contains Name (4 Ways)
How to Delete Hidden Sheets in Excel (2 Effective Methods)

2. VBA Code to Delete All Sheets Except One in Excel

You can also use the VBA code to delete all sheets except one in Excel. This time we will keep the active sheet intact and delete the rest of the sheets.

Steps:

First, opening the workbook, press the ALT + F11 key from the keyboard.

Second, a new window will appear named “Microsoft Visual Basic for Applications”.
Next, click “Module” from the “Insert” option.

In the new module place the following code and hit the “Run” icon-

Sub delete_all_sheets()
Dim deleting_sheet As Worksheet
Application.DisplayAlerts = False
For Each deleting_sheet In Sheets
If deleting_sheet.Name <> TotalSales Then
deleting_sheet.Delete
End If
Next deleting_sheet
Application.DisplayAlerts = True
End Sub

VBA Explanation:

First, we created a sub procedure named- “delete_all_sheets“.
Second, declared variable deleting_sheet where this will store the worksheet.
Next, Applicaion.DisplayAlerts=False in this part, when we try to delete sheet from a workbook, Excel will provide a warning message. Thus we are setting the DisplayAlerts system to False so that it don’t appear while deleting.
Here, For Each deleting_sheet In Sheets → In this section, we will travel through all the worksheet and delete the worksheets.
If deleting_sheet.Name<>TotalSales Then deleting_sheet.Delete → In this loop, if the sheet name is “Total Sales” then the loop will avoaid deleting the sheet.
Next deleting_sheet Application.DisplayAlerts=True→ Here, the loop will go through other worksheets and delete them.

In conclusion, we have successfully deleted all sheets except one in Excel. Simple isn’t it?

Read More: Excel VBA: Delete Same Sheet from Multiple Workbooks

Conclusion

In this article, I have tried to cover all the methods to delete all sheets except one. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the ExcelDemy team, are always responsive to your queries. Stay tuned and keep learning.

How to Undo Delete Sheet in Excel (3 Methods)
Shortcut to Delete Sheet in Excel (5 Quick Examples)
Excel VBA: Delete Sheet If It Exists (4 Methods)
How to Delete Excel Sheet Using VBA (10 Suitable Ways)
[Fixed!] Delete Sheet Not Working in Excel (2 Solutions)
How to Delete a Sheet in Excel (5 Ways)

How do I delete multiple worksheets in Excel?

Delete one or more worksheets.

Select the worksheet or worksheets that you want to delete. Tip: When multiple worksheets are selected, [Group] appears in the title bar at the top of the worksheet. ... .

On the Home tab, in the Cells group, click the arrow next to Delete, and then click Delete Sheet..

How do I delete multiple sheets in Excel using the keyboard?

Steps to Delete Sheet Using Keyboard Shortcut.

Go to the spreadsheet or select the multiple spreadsheets tabs together if you want to delete those selected sheets in one go..

After that, Press the “Alt → H → D → S” or “Alt → E → L” keys in a sequence and you will get the sheets deleted..

How do I delete multiple unhide sheets in Excel?

Use the Document Inspector to Remove Hidden Data To open the Document Inspector, click File > Info > Check for Issues > Inspect Document. The Excel Document Inspection window shown below opens up. Click Inspect to identify hidden content, and then click Remove All to remove the item of your choice.

How do you delete everything on sheets?

To clear all contents, formats, and comments that are contained in the selected cells, click Clear All. To clear only the formats that are applied to the selected cells, click Clear Formats. To clear only the contents in the selected cells, leaving any formats and comments in place, click Clear Contents.

Cara Belajar How Xlwings delete sheet VBA Delete tabs