Search for Sheet Names

[rossbritton26]

Hi,

I have a spreadsheet with around 200 sheets in, each sheet has a name containing a policy number and there is normally another small part such as 01-000. I have the following code:

Sub FindPolicy()
Dim SearchData As String
SearchData = InputBox("Enter policy number here")
If SearchData <> vbNullString Then
On Error Resume Next
Sheets(SearchData).Activate
If Err.Number <> 0 Then MsgBox "Cannot Find The Specified Policy Number " & SearchData
On Error GoTo 0
End If
End Sub


This code works when i know the exact name of the sheet i am looking for. However, i do not always know the exact name as the additional numbers at the end of the name vary, and can sometimes be in a different format. Also, there may be more than one sheet for each policy, with a different code at the end. Ideally, i'd search for a policy number and would then be able to choose from a list of sheets for that number.

Any ideas?
Thanks,
Ross

 

[villy]

My suggestion is make an index sheet let say the first sheet then make some code to enter all the names of sheets then make a combo box to choose from and what ever the value you choose it will activate..Want to try?

 

[rossbritton26]

thanks for the reply,

not quite sure i get where your coming from though, could you explain further??

 

[villy]

Try this...

Sub EmptySheet()
For i = 1 To Sheets.Count
    Range("Z" & i).Value = Sheets(i).Name
Next
End Sub

This will show all the sheets name you have...make sure you run this in an emptysheet as your index sheet then make a data validation to A2 then select list pick up ranges of Col. Z
You will now have a drop down list then..
You can say A2 to activate sheet name correspondece to your choice..

 

[rossbritton26]

thanks again,

that code works well, i have created a dropdown list, thank you.

Now is there any way i can limit the values in the dropdown box to values only containing a specific word/number??

Thanks again,
Ross

 

[villy]

Limit? in what sense? for example? because that code only gives you the exact name of your sheets otherwise we have to modify something depends on what you want

 

[rossbritton26]

well i have a large number of sheets, an example of one of my names is ANNC030475 02-000, where the first part(ANNC030475) is the reference no. and the second part(01-000) is where for whatever reason they have more than one sheet, however, the second part isnt always in the same format as i get sent it from elsewhere, so i'd like to type in the first part, e.g. ANNC030475 and see all the sheets containing that part e.g. ANNC030475 02-000, ANNC030475 05-125, ANNC030475 03/124.

Hope this helps.