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