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?
Recommended Excel Productivity Tools
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: