How to create a sheet if not exist in the workbook?
Supposing, you have a workbook which contains multiple worksheets, now, you need to determine if a specific worksheet name exists. If the worksheet name does not exist, please create it. If it exists, please give a prompt message to tell you the sheet exists. How could you solve this task in Excel?
- 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.
The following VBA code can help you to check a specific worksheet name if exists in the workbook, if not, the code may create the sheet as you need.
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: Create a sheet if not exist in workbook:
Sub TestSheetCreate() 'Updateby Extendoffice 20160922 Dim newSheetName As String Dim checkSheetName As String newSheetName = Application.InputBox("Input Sheet Name:", "Kutools for Excel", _ "sheet4", , , , , 2) On Error Resume Next checkSheetName = Worksheets(newSheetName).Name If checkSheetName = "" Then Worksheets.Add.Name = newSheetName MsgBox "The sheet named ''" & newSheetName & _ "'' does not exist in this workbook but it has been created now.", _ vbInformation, "Kutools for Excel" Else MsgBox "The sheet named ''" & newSheetName & _ "''exist in this workbook.", vbInformation, "Kutools for Excel" End If End Sub
3. And then press F5 key to run this code, a prompt box is popped out to remind you enter the sheet name that you want to check, see screenshot:
4. Then click OK button:
(1.) If the sheet exists, a prompt box will pop out to remind you as following screenshot shown:
(2.) If the sheet does not exist, it will be created at once, see screenshot: