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?
Create a sheet if not exist in workbook with VBA code
Create a sheet if not exist in workbook with VBA code
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
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:
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!
