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.
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
- In Step 1, the macro first declares an object called ws. This 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 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.
- In Step 3, the macro simply compares the active sheet name to the sheet that is currently being looped.
- 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.
- 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:
- Activate the Visual Basic Editor by pressing
- Right-click the project/workbook name in the Project window.
- Type or paste the code in the newly created module.