Delete All Worksheets Except Active One

Sometimes, you may want to delete all Worksheets except the active one, if the Workbook have many Worksheets, it seems very boring to do it manually. In these situations, you can use this simple macro.

VBA Code

Sub DeleteAllWorksheetsExceptActive()
'Step 1:  Declare your variables
    Dim ws As Worksheet
'Step 2: Start looping through all worksheets
    For Each ws In .Worksheets
 
'Step 3: Check each worksheet name
    If ws.Name <> .ActiveSheet.Name Then
 
'Step 4: Turn off warnings and delete
    Application.DisplayAlerts = False
    ws.Delete
    Application.DisplayAlerts = True
    End If
   
'Step 5:  Loop to next worksheet
    Next ws
End Sub

How This Macro Works

  1. In Step 1, the macro first declares an object called ws. This creates a memory container for each worksheet it loops through.
  2. In Step 2, the macro begins to loop, telling Excel it will evaluate all worksheets in this workbook.There is a difference between ThisWorkbook and .
    • The ThisWorkbook object refers to the workbook that the code is contained in. The ActiveWorkbook object refers to the workbook that is currently active. They often return the same object, but if the workbook running the code is not the active workbook, they return different objects. In this case, we don’t want to risk deleting sheets in other workbooks, so we use ThisWorkbook.
    • Note that when you use ThisWorkbook in a macro instead of ActiveWorkbook, you can’t run the macro from the Personal Macro Workbook. This is because ThisWorkbook refers to the Personal Macro Workbook, not the workbook to which the macro should apply.
  3. In Step 3, the macro simply compares the active sheet name to the sheet that is currently being looped.
  4. In Step 4, if the worksheet names are different with the active sheet name, the macro deletes the worksheet. We use the DisplayAlerts method turns off Excel’s warnings so you don’t have to confirm each delete.
  5. In Step 5, the macro loops back to get the next worksheet. After all sheets are evaluated, the macro ends.

How to Use This Macro

To use this macro, you can copy and paste it into a standard module:

  1. Activate the Visual Basic Editor by pressing ALT+F11.
  2. Right-click the project/workbook name in the Project window.
  3. Choose Insert -> Module.
    Insert Module
  4. Type or paste the code in the newly created module.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *