How to delete sheet if name contains specific text from workbook?
Supposing, you have a workbook which contains multiple worksheets, and now, you want to delete the sheets by tab names. For example, if the sheet name contains a specific text “KTE”, then delete it, and then get the following screenshot. How could you delete the sheets if name contains a specific text immediately without deleting them one by one?
Recommended Excel Productivity Tools
Here, I will create a VBA code for you to delete the sheets by name, please do as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Delete sheets if name contains specific text
Sub Deletebyname() 'Updateby Extendoffice 20160930 Dim shName As String Dim xName As String Dim xWs As Worksheet Dim cnt As Integer shName = Application.InputBox("Enter the specific text:", "Kutools for Excel", _ ThisWorkbook.ActiveSheet.Name, , , , , 2) If shName = "" Then Exit Sub xName = "*" & shName & "*" ' MsgBox xName Application.DisplayAlerts = False cnt = 0 For Each xWs In ThisWorkbook.Sheets If xWs.Name Like xName Then xWs.Delete cnt = cnt + 1 End If Next xWs Application.DisplayAlerts = True MsgBox "Have deleted" & cnt & "worksheets", vbInformation, "Kutools for Excel" End Sub
3. Then press F5 key to run this code, and a prompt box is popped out to remind you enter the specific text that you want to delete sheet tab based on, see screenshot:
4. And then click OK, all the sheets which name contains the specified text “KTE” have been deleted at once. See screenshot: