To check if a sheet exists in a workbook or not using VBA Excel.
In this VBA excel automation, we had a workbook which contained 2 sample worksheets, i.e. Test_1 and Test_2 and our objective was to check if there is a worksheet named as Test_2 exists in this workbook or not. We can see the tabs for both the worksheets in the image below.
In the code below we have created a variable called MySheetName, it contained the value “Test_2”, which is the name of the sheet which we want to find in the workbook. Then we loop though each sheet in the workbook. In each iteration of For loop we check if the currently referred sheet has the same name as that of value of variable MySheetName.
If value in variable MySheetName is equal to name of currently referred sheet, then a message box will be displayed, which will confirm that sheet exists, we can see the same in the image below.
Sub CheckSheetExists() Dim wSheet As Worksheet Dim MySheetName As String 'Sheet name to be checked MySheetName = "Test_2" 'Loop through each worksheet For Each wSheet In ThisWorkbook.Sheets 'If sheet was found then display the message box If wSheet.Name = MySheetName Then MsgBox "Sheet " & MySheetName & " Exists" End If Next wSheet End Sub
Since worksheet Test_2 exists, a message box is displayed as shown below.
- We can also get the name of sheet by input box as well.
Post you may like