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?
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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: