Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

 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


arrow blue right bubble 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 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:

doc create sheet if not exist 1

4. Then click OK button:

(1.) If the sheet exists, a prompt box will pop out to remind you as following screenshot shown:

doc create sheet if not exist 2

(2.) If the sheet does not exist, it will be created at once, see screenshot:

doc create sheet if not exist 3



Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: 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!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.