User form to query (up to) 8 worksheets

[Mel Smith]

I have a workbook containing 8 identical sets of data, all laid out with the same persons occupying the same row and the same data occupying the same columns.

My question is, is it possible for a userform to ‘pull’ data from all 8 sheets? What I’m trying to achieve is to search on one name and the userform to then populate with all 8 sets of data in respect of that person.

Thanks for your help.

Mel

 

[My Aswer Is This]

I thought I asked a very simple question. Here it is again.
If I enter Alpha into the search box will I find Alpha in column A of all 8 sheets?
If the answer is no then you will get a error message

 

[Mel Smith]

The entries in column A haven't changed. All the testing I did was using one of the 25 names currently displayed. There always has been a further 35 entries - numbers from 26-60. I didn't test them previously as, understandably, they have no current data attached - all the field in the UserForm would have been blank. This afternoon I put myself in in place of the last 'player', number 60, and that was when I discovered the error. I then tested all the other numbers.
Mel

 

[Mel Smith]

If I enter a name that is currently listed in the range A4:A63 everything is fine. If I enter one of the numeric "names" I get an error e.g. "26 Notfound". The same thing occurred when i put myself in in the last cell, A63. There was, still the numeric "names" (26:59) between the last current player and my name.

mel

 

[Mel Smith]

I've overcome this issue by changing the numeric 'names' - "26-60" to alpha numeric "TBA 1" etc. I then put myself instead of TBA 35, did a search via UserForm1 and 2 using TBA number, number etc and also my own name and all worked as was expected, with no error messages.

Mel

 

[My Aswer Is This]

I'm glad you have it sorted out.
The script searches the entire column A of all 8 sheets not just a range.

 

[My Aswer Is This]

I'm still not clear of a value you were looking for in column A which caused a error.
Would you please give a example of a value you entered in the search box that caused a error.

 

[Mel Smith]

I'm sorry about the delay in replying - time difference between EST and GMT! The data in cells A4:63 are all contained data (pulled in from another workbook. The data is contiguous. Cells A4:A28 contained names of current personnel set out as JE Bloggs, L Jones etc. Cells A29:A63 contained numbers (to be changed to names, as and when) set out as "26" to "60" (without the inverted commas). Searching on a name works perfectly, thank you. However, searching on, say 33 or 49 etc. 'throws' up the error message. Similarly, when I put my name in cell A63 the error message was displayed but not if I put it immediately following the previous name (i.e. in cell A29)

 

[My Aswer Is This]

Thanks for that answer even if I do not understand it.
In your original post I just thought you have names like Bob or George or John in column A
But I surely do not understand:
Cells A29:A63 contained numbers (to be changed to names, as and when) set out as "26" to "60" (without the inverted commas)

I do not understand how numbers can be changed to names.
But glad you have things worked out. Take care

 

[Mel Smith]

Thank you, too for all your help. You’ve taught me a lot. Perhaps, for reasons I do not understand, the search process in the UserForm works with Alphabetical and Alphanumeric but not Numeric data. That seems to be the case. Quite why it ‘failed’ when my name was added to the last cell in the range I don’t know either. Perhaps it was because there were Numeric entries between the two Alphabetical entries.
Mel

 

[My Aswer Is This]

Well when I use it it works for me even when I use a value like this: 789-467-Ape

I asked for you to show me a value that did not work and you did not answer my question.