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

Insert into a new module to apply the code above.

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.

📌 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.

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

Thus, a new module named Module1 has been created.

📌 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

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.

Afterward, press Ctrl + S.

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

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.

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.

📌 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.

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

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

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

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

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

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

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

Read More: Excel VBA: Add Sheet After Last

Download Practice Workbook

You can download our practice workbook from here for free!

Add Sheet if It Does Not Exist.xlsm

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.