Most of the time we face a situation where we need to delete multiple sheets for a workbook. it become a monotonous job when there are large number of sheets which should be deleted. Sometime, because of repetitive task user accidentally deletes useful worksheets. As a result, it consumes lot of productive time which is not a worth in reporting project.









This issue can be overcome using VBA macro which will be used to delete selected worksheet from activeworkbook.Which will result into save productive time and ensure data quality.

Here is the code which will be used to delete selected sheets from active workbook:

Option Explicit

Sub Delete_Selected_Sheets()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' http://vbatricksntips.com

'Declare Variables used in macro code
Dim shtArray() As String
Dim shtDelArray() As String
Dim shtList As String
Dim i As Integer
Dim NumOfSelectedSheet As Long
Dim Sheets_to_Delete As String
Dim shtSelected, shtDelete As String
Dim sChar, eChar As Integer

On Error GoTo Err:

Application.DisplayAlerts = False
Application.ScreenUpdating = False

'Loop to retrieve list of worksheets in active workbook
shtList = ""
For i = 1 To ActiveWorkbook.Sheets.Count
    shtList = shtList & Chr(34) & ActiveWorkbook.Sheets(i).Name & Chr(34) & ","
Next i

'Ask user to enlist worksheet for deletion
shtList = Left(shtList, Len(shtList) - 1)

Sheets_to_Delete = InputBox(prompt:="Adjust the list of sheets which you want to delete ?" & vbNewLine & vbNewLine & "For Example: " & vbNewLine & Chr(34) & "Test_Sheet1" & Chr(34) & "," & Chr(34) & "Test_Sheet2" & Chr(34) & "," & Chr(34) & "Test_Sheet3" & Chr(34), Title:="Delet Worksheets", Default:=shtList)
NumOfSelectedSheet = Len(Sheets_to_Delete) - Len(Replace(Sheets_to_Delete, ",", "")) + 1
sChar = 1

'Loop to delete selected worksheet in inputbox
For i = 1 To NumOfSelectedSheet
    
    If i < NumOfSelectedSheet Then
    eChar = Find_N(",", Sheets_to_Delete, i)
    Else
    eChar = Len(Sheets_to_Delete) + 1
    End If
    
    shtSelected = Mid(Sheets_to_Delete, sChar, eChar - sChar)
    shtDelete = Right(Left(shtSelected, Len(shtSelected) - 1), Len(Left(shtSelected, Len(shtSelected) - 1)) - 1)
    ActiveWorkbook.Sheets(shtDelete).Delete
    sChar = eChar + 1
Next i

Application.DisplayAlerts = True
Application.ScreenUpdating = True

MsgBox "Selected worksheets deleted successfully !!!", vbInformation

'Error handling code for runtime errors
Err:
    If Err.Number > 0 Then
    
    MsgBox "An error has occured. See below error desciption for details." & vbNewLine & vbNewLine & "VBA Error No: " & Err.Number & vbNewLine & "VBA Error Description: " & Err.Description
    End If
    
End Sub

Function Find_N(tFind_What As String, tInput_String As String, N As Integer) As Integer
' Author: Dreams24
' Written for VBA Tricks and tips blog
' http://vbatricksntips.com

Dim i As Integer
Application.Volatile

Find_N = 0

For i = 1 To N
Find_N = InStr(Find_N + 1, tInput_String, tFind_What)
If Find_N = 0 Then Exit For
Next i

End Function

If you do not want to write this code again and again, you can use Delete Worksheet Add-ins.
To get your own Add-ins just navigate to "Downloads" screen in VBA Tricks and Tips application's Main window.