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
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
Michael - great !!!
My Pleasure, and thanks for the feedback....
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 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.eliW
Well-known
Member
Just what I needed and more!
I know now how to get rid of the annoying popup for confirming deletion
Thank you,
You could also change
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-
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.
Related Articles
- 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)