Wasim Akram
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.
