How to delete duplicate worksheets usin vba?-CodePudding

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.

CodePudding user response：

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!

CodePudding user response：

And such way is possible

Sub DelDubl()
Dim wsh As Worksheet, j As Long, wshNm

' array with names of worksheets which are not to delete
wshNm = Array("Asutosh")

For Each wsh In ThisWorkbook.Worksheets
            
            For j = LBound(wshNm) To UBound(wshNm)
            If wsh.Name Like wshNm(j) Then GoTo NXT1
            Next j
            
            Application.DisplayAlerts = False
            wsh.Delete
            Application.DisplayAlerts = True
NXT1:

Next


End Sub