How to delete duplicate sheets in a workbook using VBA?

To delete duplicate sheets in an Excel workbook using VBA, you can write a macro that checks for sheets with the same names and deletes any duplicates. Below is a step-by-step guide and a sample VBA code to achieve this:

Step-by-Step Guide

Open the Workbook: Open the workbook where you want to delete duplicate sheets.
Access the VBA Editor: Press `ALT + F11` to open the Visual Basic for Applications (VBA) editor.
Insert a Module: In the VBA editor, go to `Insert` > `Module` to insert a new module.
Copy the VBA Code: Copy and paste the VBA code provided below into the module.
Run the Macro: Close the VBA editor and return to Excel. Press `ALT + F8`, select the macro you want to run (e.g., `DeleteDuplicateSheets`), and click `Run`.

VBA Code

Here is a sample VBA code that will delete duplicate sheets within a workbook:

Sub DeleteDuplicateSheets()
    Dim wsNames As Collection
    Dim ws As Worksheet
    Dim i As Long
    Dim sheetName As String

    On Error Resume Next
    Set wsNames = New Collection

    ' Store unique sheet names in the collection
    For Each ws In ThisWorkbook.Worksheets
        Err.Clear
        sheetName = ws.Name
        wsNames.Add ws, ws.CodeName
        If Err.Number = 0 Then
            ' First encounter of the sheet name, save it in the collection
        Else
            ' Duplicate sheet name found, delete the current sheet
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
    Next ws
End Sub

Explanation

wsNames Collection: This is used to store unique worksheet names. Collections in VBA do not allow duplicate keys, which makes it useful for identifying duplicates.
Error Handling: `On Error Resume Next` is used to bypass runtime errors. If an error occurs while adding a sheet to the collection, it implies the sheet name already exists (hence duplicate).
Deleting Duplicates: Duplicate sheets trigger an error when added to the collection, signaling the code to delete the sheet.
DisplayAlerts: The property is set to `False` to prevent Excel from displaying confirmation alerts when deleting sheets. Make sure to set it back to `True` afterwards to ensure normal warnings are shown.

Before using this script, make sure to back up your workbook, as running the macro will permanently delete sheets. Also, adapt and test the VBA code on a sample workbook if needed, as the VBA environment settings or workbook details might vary.

facebooklinkedin