Excel VBA to Add Sheet If It Does Not Exist (with Quick Steps)

Excel VBA is a powerful and handy tool to perform tasks pretty quickly in a bulk amount.  You can apply several conditions and derive different results on the basis of the individual conditions through VBA. Now, sometimes, you may want to check if a particular sheet exists in your workbook. And, if not, you might need to create that sheet. In this article, I will show you all the steps to add a sheet if it does not exist, using Excel VBA.


Excel VBA: Add Sheet If It Does Not Exist (A Quick View)

Sub AddSheetIfNotExist()
Dim addSheetName As String
Dim requiredSheetName As String
addSheetName = Application.InputBox("Which Sheet Are You Looking For?", _
"Add Sheet If Not Exist", "Sheet5", , , , , 2)
On Error Resume Next
requiredSheetName = Worksheets(addSheetName).Name
If requiredSheetName = "" Then
Worksheets.Add.Name = addSheetName
MsgBox "The ''" & addSheetName & _
"'' sheet has been added as it did not exist.", _
vbInformation, "Add Sheet If Not Exist"
Else
MsgBox "The ''" & addSheetName & _
"''sheet already exists in this workbook.", _
vbInformation, "Add Sheet If Not Exist"
End If
End Sub

Excel VBA Code to Add Sheet If Not Exist

Insert into a new module to apply the code above.


Download Practice Workbook

You can download our practice workbook from here for free!


Steps to Apply VBA Code to Add Sheet in Excel If It Does Not Exist

Say, you have a workbook containing 4 worksheets named January, February, March, and April. Each sheet contains the following month’s sales report. Now, you need to find some sheets in the workbook and add the sheet if it does not exist. You can follow the step-by-step guidelines below to accomplish this.

Dataset to Apply Excel VBA Code to Add Sheet If Not Exist


📌 Step 1: Insert a New Module

First, you need to insert a module to write VBA code.

  • To do this, at the very beginning, go to the Developer tab >> Visual Basic tool.

Choose the Visual Basic Tool

  • As a result, the Microsoft Visual Basic for Applications window will appear.
  • Subsequently, go to the Insert tab >> Module tool.

Insert a New Module to Apply Excel VBA to Add Sheet If Not Exist

Thus, a new module named Module1 has been created.

Read More: How to Add Sheet with Name in Excel VBA (6 Easy Ways)


Similar Readings


📌 Step 2: Write and Save the Required VBA Code

Now, you need to write the code inside the module and save it.

  • In order to do this, click on the Module1 option and write the following code in the code window.
Sub AddSheetIfNotExist()
Dim addSheetName As String
Dim requiredSheetName As String
addSheetName = Application.InputBox("Which Sheet Are You Looking For?", _
"Add Sheet If Not Exist", "Sheet5", , , , , 2)
On Error Resume Next
requiredSheetName = Worksheets(addSheetName).Name
If requiredSheetName = "" Then
Worksheets.Add.Name = addSheetName
MsgBox "The ''" & addSheetName & _
"'' sheet has been added as it did not exist.", _
vbInformation, "Add Sheet If Not Exist"
Else
MsgBox "The ''" & addSheetName & _
"''sheet already exists in this workbook.", _
vbInformation, "Add Sheet If Not Exist"
End If
End Sub

Write Required Code Inside Module

  • As a result, the code window will look like the following.

🔎 Code Explanation:

Segment 1:

Sub AddSheetIfNotExist()
Dim addSheetName As String
Dim requiredSheetName As String

In this part, we have declared the macro name and variable names.

Segment 2:

addSheetName = Application.InputBox("Which Sheet Are You Looking For?", _
"Add Sheet If Not Exist", "Sheet5", , , , , 2)
On Error Resume Next
requiredSheetName = Worksheets(addSheetName).Name

In this part, we have created an input box. Through this input box, we can take the input of the name of the file that we need to find.

Segment 3:

If requiredSheetName = "" Then
Worksheets.Add.Name = addSheetName
MsgBox "The ''" & addSheetName & _
"'' sheet has been added as it did not exist.", _
vbInformation, "Add Sheet If Not Exist"

In this part, we check if the required sheet exists in the workbook. If not, it would create the required sheet and show us a message about this change.

Segment 4:

Else
MsgBox "The ''" & addSheetName & _
"''sheet already exists in this workbook.", _
vbInformation, "Add Sheet If Not Exist"
End If
End Sub

In this part, we have worked with the result if the required sheet already exists in the workbook. In this scenario, another message box will appear informing you that this sheet exists. Furthermore, in this part, we end the code properly.

Excel VBA Code to Add Sheet If Not Exist

  • Afterward, press Ctrl + S.

Save the VBA Code

  • Subsequently, a Microsoft Excel window will appear. Click on the No button.

Choose No to Enable Macro

  • As a result, the Save As window will appear.
  • Following, choose the Save as type: option as .xlsm format. Subsequently, click on the Save button.

Save the Excel as .xlsm Format

Thus, you have written and saved your required code.

Note:

You must save the Excel workbook in .xlsm format. Otherwise, the macro won’t be enabled and the code would not work.

Read More: Excel VBA to Add Sheet with Variable Name (5 Ideal Examples)


📌 Step 3: Run the Code

Now, you need to run the code and check the results.

  • To do this, first and foremost, click on the Run icon in the Microsoft Visual Basic for Applications window.

Run the Required VBA Code

  • As a result, the Macros window will appear.
  • Subsequently, choose the AddSheetIfNotExist macro and click on the Run button.

Run the Required Macro to Add Sheet If Not Exist By Excel VBA

  • At this time, our created message box named Add Sheet If Not Exist will appear. Here, the auto option would be Sheet5.

Default Input Box

  • Now, for checking, write “April” in the text box and click on the Ok button.

Check If "April" Sheet Exists

  • Subsequently, you would see that another message box would appear telling you that the sheet already exists.
  • Click on the Ok button.

Result of Checking

  • Afterward, run the code again and write “May” in the text box of the created message box. Subsequently, click on the OK button.

Check If "May" Sheet Exists

  • As a result, you will see another message box that will appear informing you that the “May” sheet didn’t exist and thus it created this sheet.
  • Following, click on the OK button.

Confirm the Result of Checking

Finally, you can see you have added a sheet that didn’t exist before. And, the workbook would look like this now.

Added Sheet with Excel VBA

Read More: Excel VBA: Add Sheet After Last (3 Ideal Examples)


Conclusion

So, in this article, I have shown you all the steps to add a sheet if not exist with Excel VBA. Go through the full article carefully to understand it better and accomplish your desired result. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to contact me.

And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo