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.
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.