How to Check if Sheet Exists in VBA

1. Check if Sheet Exists in VBA
2. Check if Sheet Exists in Closed Workbook in VBA

We will introduce how to check if a sheet exists using VBA in Excel.

Check if Sheet Exists in VBA

While working on multiple sheets, there may be some duplicate sheets, or we may want to save ourselves from creating any duplicate sheets. For this purpose, we can use VBA to check if there are any sheets with the same name for which we may want to create a sheet.

To check whether the sheet exists, we require a loop along with each sheet in the workbook and compare the names we have created. We will use two loops; one will be the For each loop, and the second one will be the If Else statement.

First, we will create two variables: one will store the worksheet, and the second one will store the sheet’s name for which we want to look for duplicates or check if the sheet with this name exists. We will use the for loop to go through all the files in a folder and the if-else statement to check if the sheet’s name exists with the same name as the other sheet.

If the file exists with the same name, we will show a message box that says that the sheet exists. Otherwise, the message box will display that the sheet doesn’t exist.

Code:

#VBA
Sub sheetCheck()

Dim sheet As Worksheet
Dim Name As String

Name = "Sheet1"

For Each sheet In ThisWorkbook.Worksheets

    If sheet.Name = Name Then

            MsgBox "Yes! " & Name & " is there in the workbook."
            Exit Sub

    End If

Next sheet

MsgBox "No! " & Name & "is not there in the workbook."

End Sub

Output:

When we searched for sheet1, we got the positive response that the sheet with the same name already exists.

Check if Sheet Exists in Closed Workbook in VBA

There may be some situations where we may also want to check for the sheets in an excel file that are closed or on which we have already worked. We can also check for the sheets in closed excel files by opening the files using the VBA and the For each loop with the If else statement as we used in the above example.

Let’s try to check for the sheet in a closed excel file.

Code:

#VBA
Sub checkSheet()

Dim book As Workbook
Dim sheet As Worksheet
Dim Name As String

Name = "Sheet1"

Application.ScreenUpdating = False

Set book = Workbooks.Open _
("C:\Users\Hp9470\Documents\Book2.xlsx")

For Each sheet In book.Worksheets

    If sheet.Name = Name Then

        book.Close SaveChanges:=True
        MsgBox "Yes! Sheet Exists"
        Exit Sub

    End If

Next sheet

Application.ScreenUpdating = False

MsgBox "No! Sheet doesn't exists"

End Sub

Output:

The sheet exists in the closed excel file we accessed using the open function. In this way, we can check both opened and closed files about sheets using the name way.