How to delete specific sheet if exists in workbook?
How could you delete a specific worksheet if it exists within a workbook? Normally, you need to find and check it if exists, and then delete it manually. Here, I will talk about an easy way to delete the specified worksheet directly if it exists in the Excel file.
Delete a specific worksheet if exists in a workbook with VBA code
Delete a specific worksheet if exists in a workbook with VBA code
The following VBA code can help you to delete the specified worksheet if it exists in the workbook, 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 a specific worksheet if exists in workbook
Sub Test()
'Updateby Extendoffice
Dim xWs As Worksheet
Dim sheetName As String
sheetName = Application.InputBox("Input Sheet Name:", "Kutools for Excel", _
"sheet1", , , , , 2)
Application.DisplayAlerts = False
Err.Clear
On Error Resume Next
Set xWs = Sheets(sheetName)
If Err <> 0 Then
MsgBox "The'" & sheetName & "'" & "does not exist!", vbInformation, "Kutools for Excel"
Exit Sub
Else
xWs.Delete
MsgBox "The'" & sheetName & "'" & "has been deleted!", vbInformation, "Kutools for Excel"
End If
Application.DisplayAlerts = True
End Sub
3. Then press F5 key to run this code, a prompt box is displayed to remind you type the sheet name that you want to check if exist, see screenshot:
4. And then click OK button:
(1.) If the sheet exists, it will be delete at once, see screenshot:
(2.) If the sheet does not exist, a prompt box will pop out to remind you as following screenshot shown:
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
