How To Delete All Sheets Except Specified/Current One In Excel ?


Excel is a strong programme that is frequently used for data management and analysis. You might need to erase superfluous sheets when working on complex workbooks with many sheets in order to simplify and streamline your workbook. Manually deleting sheets can take a while, especially if there are a lot of sheets to do so. In this tutorial, we'll walk you through the process of deleting all Excel sheets other than the one you've selected or are now viewing. This strategy will enable you to complete the task swiftly and effectively whether you want to keep just one particular page or the one you are working on right now.

You can learn how to declutter your Excel workbook by deleting all superfluous sheets by adhering to the guidelines offered in this article. Let's examine how to delete unwanted Excel sheets while keeping the crucial ones.

Delete All Sheets Except Specified/Current One

Hear how we will first create a VBA module and then run it to complete the task. So let us see a simple process to know how you can delete all sheets except the specified or current one in Excel.

Step 1

Consider an Excel workbook containing multiple worksheets.

First, right−click on any sheet name and select View Code to open a VBA application. Then, click on Insert, select Module, and copy the below code into the textbox as shown in the below image.

Right click > View code > Insert > Module > Copy.

Code

Sub DeleteSheets1()
   Dim xWs As Worksheet
   Application.ScreenUpdating = False
   Application.DisplayAlerts  = False
   For Each xWs In Application.ActiveWorkbook.Worksheets
      If xWs.Name <> "Sheet1" And xWs.Name <> "Sheet2" Then
         xWs.Delete
      End If
   Next
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True
End Sub

Step 2

Then save the sheet as a macro−enabled template and click F5 to run the code and complete the task.

Save > F5.

Note

The sheets in the current worksheet that you want to maintain are "Sheet1" and "Sheet2." They can be modified to suit your needs.

Please remove the all And condition from the code if you just want to keep one page and erase the others from the worksheet.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can delete all sheets except the specified or current one in Excel to highlight a particular set of data.

Updated on: 07-Jul-2023

524 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements