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
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
MsgBox "Worksheet Found and activated"
Responce = MsgBox("Worksheet with the name '" & WorkSheetName & "' not found", vbOKOnly)
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.