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 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.
Delete All Sheets Except One in Excel: 2 Simple Methods
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
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.
- Then, 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“.
- Next, declared variable deleting_sheet will store the worksheet.
- Then, 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.
- Lastly, we have successfully deleted all sheets except one in Excel. Simple, isn’t it?
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, I have covered 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.
