While working with Visual Basic in Excel many times it is required to find a certain worksheet by its name and/or activate the worksheet using VB. Here is the solution for how to find a worksheet by its name using Visual Basic for Applications in Excel.
First make a User Form in Visual Basic Editor, and add a command button on this form.
Name the command button to SearchSheets.
Then double click on the form to open the code editor.
Add this function there
Function SheetExists(WorkSheetName As String) As Boolean Dim WSName As Worksheet On Error Resume Next Set WSName = Worksheets(WorkSheetName) If Not WSName Is Nothing Then SheetExists = True End Function
After that assign this code to the button you just created.
Private Sub SearchSheets_Click() Dim WorkSheetName As String WorkSheetName = "Your Value" If SheetExists(WorkSheetName) Then Worksheets(WorkSheetName).Activate MsgBox "Worksheet Found and activated" Else Responce = MsgBox("Worksheet with the name '" & WorkSheetName & "' not found", vbOKOnly) End If End Sub
Now run the script and press the Search Worksheets button.
If there is a worksheet named “Your Value“(change it to your own) is available then the worksheet will bw activated and you will see this massage “Worksheet Found and activated”.
And if the worksheet is not Available then you will see this massage “Worksheet with the name ‘Your Value’ not found“.
That is it! Hope this helped you.