Excel Macro: 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.

Delete All Worksheets Except Active One

'---------------- Modules ----------------
Sub DeleteAllWorksheetsExceptActive()
'Step 1:  Declare your variables
    Dim ws As Worksheet
'Step 2: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets
 
'Step 3: Check each worksheet name
        If ws.Name <> ThisWorkbook.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 ActiveWorkbook.
   • 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 property 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