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

 

[Mel Smith]

I've found the problem. I checked each textbox in turn, after having (re)numbered them from 1-100 and found a typo on box 73, is showed 732. Problem solved and the UserForm code worked like a charm!

Do I now paste this same code into UserForm 2 and change the array to be =Array(6, 7, 8, 10)?

Mel

 

[My Aswer Is This]

I've found the problem. I checked each textbox in turn, after having (re)numbered them from 1-100 and found a typo on box 73, is showed 732. Problem solved and the UserForm code worked like a charm!

Do I now paste this same code into UserForm 2 and change the array to be =Array(6, 7, 8, 10)?

Mel

Yes I believe that should work.
Just modify the array

Let me know how things work out.
This worked out well it looks like.

 

[Mel Smith]

YOU ARE AN ABSOLUTE STAR!
I changed the array to 6, 7, 8, 10 and it ran perfectly. Thank you so very much. Both UserForms now run as desired.
This project of mine started to germinate some time ago and I mistakenly thought I could figure it out. Ha, what little I knew!
You have shown me how little I know and how much I need to learn. You have taught me a lot and I really am most grateful.

Many, many thanks.

Mel

 

[My Aswer Is This]

YOU ARE AN ABSOLUTE STAR!
I changed the array to 6, 7, 8, 10 and it ran perfectly. Thank you so very much. Both UserForms now run as desired.
This project of mine started to germinate some time ago and I mistakenly thought I could figure it out. Ha, what little I knew!
You have shown me how little I know and how much I need to learn. You have taught me a lot and I really am most grateful.

Many, many thanks.

Mel

I'm glad I was able to help. I learn from these projects also. It a nice challenge. I hope you understand the code somewhat and can learn from it. So I hope you see some code here you can use in your next project.

 

[Mel Smith]

I will make a point of following you!
Best wishes, Mel

 

[Mel Smith]

I was doing some testing this afternoon and have come across a small issue. You may recall that the "search" Textbox looked in Column A of the worksheet: ans = search.Value
I have tested all the names listed (in cells A4:A28) and they work as expected. In cells A29:A62 I am currently showing numbers, 26-60, to cater for incoming personnel. I added myself into Cell A63 and searched for myself using the search box and I got the "not found" error message. Similarly, I also get the same error when searching for numbers 26-60. Is there something I'm doing wrong or does the code need a tweak?

Mel

 

[My Aswer Is This]

My script searches for the value entered in the Textbox Named Search in all 8 sheets
Do you have all the same names in column A of all 8 sheets.

Or does the script only need to search one sheet for the name and then use that for all sheets.

So lets say you have "Alpha" in column A of sheet1 and the script finds "Alpha" in Row 25
Will Alpha also be found in all the other sheets on the same row.

If that is the case then we will not need to search all 8 sheets for "Alpha

We will know "Alpha" will be on the same row in all 8 sheets.
You said:
I added myself into Cell A63

Did you do that in all 8 sheets?
Because the script searches for you in all 8 sheets.

 

[My Aswer Is This]

What is in column A of all 8 sheets?
Are these names like "Alpha" and "Bravo" or do you have numbers like 456-879-1456

 

[Mel Smith]

In column A all entries on all sheets are in one or the other formats: JE Bloggs (for current players) or 27, 28 etc to 60 for incoming personnel. I put myself in in place of 60 (the last one) and it failed.

Mel

 

[My Aswer Is This]

In column A all entries on all sheets are in one or the other formats: JE Bloggs (for current players) or 27, 28 etc to 60 for incoming personnel. I put myself in in place of 60 (the last one) and it failed.

Mel

I do not understand your answer. Why would the script work before but now you say it does not work on new entries.
I do not understand:
all entries on all sheets are in one or the other formats: JE Bloggs (for current players) or 27, 28 etc to 60 for incoming personnel
Other formats ?? JE Bloggs I do not understand JE Bloggs?