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

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

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

We have a workbook containing 4 worksheets named January, February, March, and April. Each sheet contains the following month’s sales report. We need to find some sheets in the workbook and add the sheet if it does not exist.

Step 1 – Insert a New Module

Go to the Developer tab and select Visual Basic.

The Microsoft Visual Basic for Applications window will appear.
Go to the Insert tab and select Module.

A new module named Module1 has been created.

Step 2 – Insert and Save the Required VBA Code

Click on the Module1 option and insert 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

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.

Press Ctrl + S.

A Microsoft Excel window will appear. Click on the No button.

The Save As window will appear.
Choose the Save as type: option as .xlsm format.
Click on the Save button.

Note:

You must save the Excel workbook in .xlsm format.

Step 3 – Run the Code

Click on the Run icon in the Microsoft Visual Basic for Applications window.

The Macros window will appear.
Choose the AddSheetIfNotExist macro and click on the Run button.

Our created message box named Add Sheet If Not Exist will appear. The auto option would be Sheet5.

Write “April” in the text box and click on the OK button.

Another message box should appear telling you that the sheet already exists.
Click on the OK button.

Run the code again and write “May” in the text box of the created message box.
Click on the OK button.

You will see another message box that will appear informing you that the “May” sheet didn’t exist, and the code created this sheet.
Click on the OK button.