How to Delete All Sheets Except One in Excel (2 Quick Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Delete All Sheets Except One Manually

  • 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.

Delete All Sheets Except One Manually

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.

VBA Code to Delete All Sheets Except One in Excel

  • 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 Code to Delete All Sheets Except One in Excel

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?

VBA Code to Delete All Sheets Except One in Excel


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Wasim Akram
Wasim Akram

Wasim Akram, BSc, Industrial and Production Engineering, Ahsanullah University of Science & Technology, has been working with the ExcelDemy project for 10 months. Currently working as as Excel and VBA content developer who provides authentic solutions to different Excel-related problems and writes amazing content articles regularly. He published almost 150 articles and has many more coming. He is very passionate about learning new things about Microsoft office Suite and Data analysis.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo