Macro 17: Delete All but the Active Worksheet
At times, you may want to delete all but the active worksheet. In these situations, you can use this
macro.
How it works
This macro loops the worksheets and matches each worksheet name to the active sheet’s name. Each
time the macro loops, it deletes any unmatched worksheet. Note the use of the DisplayAlerts
method in Step 4. This effectively turns off Excel’s warnings so you don’t have to confirm each delete.
Sub Macro17()
‘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
1. The macro first declares an object called ws. This creates a memory container for each work-
sheet 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.
3. In this step, the macro simply compares the active sheet name to the sheet that is currently
being looped.
4. If the sheet names are different, the macro deletes the sheet. As mentioned before, we use
DisplayAlerts to suppress any confirmation checks from Excel.
5. In Step 5, the macro loops back to get the next sheet. After all sheets are evaluated, the
macro ends.
How to use it
To implement 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.
4. Type or paste the code in the newly created module.
