An Excel Macro to Delete All but the Active Worksheet - dummies

An Excel Macro to Delete All but the Active Worksheet

By Michael Alexander

At times, you may want to delete all but the active worksheet in an Excel workbook. In these situations, you can use the following macro.

How the macro works

The macro here loops through the worksheets, matching 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 property in Step 4. This effectively turns off Excel’s warnings so you don’t have to confirm each delete.

Sub Macro1()
'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
    Application.DisplayAlerts = True
    End If
'Step 5:  Loop to next worksheet
    Next ws
End Sub

The macro first declares an object called ws. This step creates a memory container for each worksheet it loops through.

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 contains the code. The ActiveWorkBook object refers to the currently active workbook. 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, you don’t want to risk deleting sheets in other workbooks, so you use ThisWorkBook.

In Step 3, the macro simply compares the active sheet name to the sheet that is currently being looped.

In Step 4, if the sheet names are different, the macro deletes the sheet. As mentioned, you use DisplayAlerts to suppress any confirmation checks from Excel. If you want to be warned before deleting the sheets, you can omit Application. DisplayAlerts = False. Omitting the DisplayAlerts statement will ensure that you get the message shown, allowing you to back out of the decision to delete worksheets.

In Step 5, the macro loops back to get the next sheet. After all the sheets are evaluated, the macro ends.

Omit the Display­Alerts statement to see warning messages.
Omit the Display­Alerts statement to see warning messages.

How to use the macro

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

  1. Activate 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.

When you use ThisWorkbook instead of ActiveWorkbook in a macro, you can’t run the macro from the personal macro workbook. Why? Because ThisWorkbook would refer to the personal macro workbook, not to the workbook to which the macro should apply.