How to delete duplicate worksheets usin vba?

Suppose I have 3 worksheets "Asutosh","Asutosh2","Asutosh3" I want to delete "Asutosh2" and "Asutosh3" using vba.

I used vba but I have to do it manually for other names such as if I record for Asutosh , other extra duplicate sheets don not delete.

>Solution :

There are two methods you can use.

If you wish to have a master sheet, that does not delete, but every other worksheet does, use something similar to the following below.

Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Closes all other worksheets par "Asutosh", saves user time not having to delete imported sheets everytime

For Each ws In ThisWorkbook.Worksheets
Application.DisplayAlerts = False

If ws.Name <> "Asutosh" Then 'If a worksheet is not named "Asutosh" it gets deleted
ws.Delete
End If

Next ws
Application.DisplayAlerts = True

MsgBox ("All sheets are deleted except specific sheet - After this, you can click either 'Save' or 'Don't Save' button") 'Message box to reasure user is okay with either option when closing the file
  
End Sub

Add this ^ to ThisWorkbook in VBA editor

Or, if you wish to delete worksheets with specific names, use the following

Sub vba_delete_sheet()
Sheets("Asutosh2").Delete
Sheets("Asutosh3").Delete
End Sub

Hope this helps!

Leave a Reply