Hi Marianne,

You should try to use whole code (not only the first procedure).
Here you have the part defining the FindAllNames function (which was
also given before).

'search for the given pattern
Private Function FindAllNames(stClName As String)
Dim sheetNo As Integer
Dim RegEx
Dim iAllNames As Integer
Dim AllNames()
iAllNames = 0

Set RegEx = CreateObject("VBScript.RegExp")
RegEx.IgnoreCase = True
RegEx.Pattern = stClName

For sheetNo = 1 To Worksheets.Count
If RegEx.test(Worksheets(sheetNo).Name) Then
ReDim Preserve AllNames(iAllNames)
AllNames(iAllNames) = Worksheets(sheetNo).Name
iAllNames = iAllNames + 1
End If
Next

If iAllNames < 0 Then
FindAllNames = AllNames
End If
End Function

Good luck,

Aga

marianne napisal(a):
Thank you for the prompt response
I tried the code but I get an error
Compile error
Sub or Function not defined:
and it highlights "FindAllNames"


" wrote:

Hello Marianne,

Please find my solution (see the code below). I modiffied a little bit
your code to open an appropriate array of sheet names.
Appropriate - means that the given pattern is searched in a set of
workbook names (function FindAllNames) .

Option Explicit

Private Sub Workbook_Open()
On Error GoTo ErrorHandler
Dim stClName As String
Dim stermes As String
Dim namesArr()


EnterClName:

stClName = InputBox("Enter the Client Name") 'it's the pattern
namesArr = FindAllNames(stClName)
Worksheets(namesArr).Select
Worksheets(namesArr(0)).Activate
Exit_Workbook_Open:
Exit Sub

ErrorHandler:

stermes = MsgBox("The pattern wasn't found. Check the spelling")
Resume EnterClName

End Sub

Best Regards,

Agnieszka