VBA to randomly select 10 employees from a list of employees

Hello everyone,

I would greatly appreciate if someone can help to create a VBA code to randomly select 10 employees from a list of employees. Every time the VBA code is executed, it should select no duplicates or repeats and then copy and paste into two worksheets with the appropriate random selection. The random selection should be based on the following criteria:
1. Employee status should be active (not on leave or suspended)
2. Scope should be included
Finally the generated worksheets (RandomList1 and RandomList2) should be broken down by shift and language. For example, RandomList1 worksheet should include 10 active employees from A shift with included scope and only 1 language. RandomList2 worksheet should include 10 active employees from B shift with included scope and only 1 language.
I have attached a sample file to demonstrate a final result.
Again, thank you for your valuable time and dedication to this wonderful forum.

Thank you so much for your quick response. Everything seems to work perfectly with one thing that RandomLlist1 worksheet should only have A shift employees and RandomLlist2 worksheet should only have B shift employees. Also, does this code ensures that every employee in the list, regardless of the number of employees, will get a chance to be selected before re-selecting a previous selected employee again? I would also like to have a 3rd worksheet (selected employees) to keep track of all the names of selected employees and the date that were selected from RandomLlist1 and RandomList2. See the attached example.
Again, thank you so much for your help!

No worries. I'm patient and thankful for your help and everyone else. You deserve a delicious coffee.

Thank you so much for your outstanding help. Both of the provided codes and files worked perfectly. I was wondering if there is a way to copy and paste the selected employees from RandomList1 and RandomList2 into selected employees worksheet to keep track of all the names of selected employees and the date that were selected?

Hello Jindon,
Thank you so much for excellent help. What part of your code do I need to update if I want to increase the number of employees in the future? Let's say that I need to include 15 instead of 10 employees?

Thank you so much GC Excel and Jindon for your help with this case. Your help has helped me tremendously. Jindon, you last code complemented to accomplish the final results.

Hi Jindon,
I tried to run your code and I am getting a Run-time error '13' Type mismatch with yellow highlighted on

Please Login or Register  to view this content.

right underneath

Please Login or Register  to view this content.

Can you point out what causing it?
Thank you again for helping

I replaced "test sub as you mentioned above and updated

Please Login or Register  to view this content.

10 to 15. I get the the Run-time error '13' Type mismatch.

I'm out at the moment, so I'll get back to you later.

Thank you so much Jindon. You are a true life saver. I noticed that when I changed the A and B for the shiftcode to 1 and 2 or I1 OR I2, the code doesn't behave as it should. If I change A and B to 1 and 2, the RandomLlist1 doesn't get any data populated. It stays blank. However RandomLlist2 gets random selected data with 2 and 1 as for the shiftcode. What part of your code analyzes and collects the shiftcode? What do I need to do if I want to use I1 and I2 instead A and B?
Again,
Thank you so much for your contribution to this Excelforum!

The code is written based on only 2 shifts. One is "A" and other, so

Please Login or Register  to view this content.

If you change "A" with the one you want to change, e.g. 1, it will create a report for 1 and the other.

I tried 1 and 2 for A and B or I1 and I2 for A and B, but data just get populated in RandomLlist2 and in Selected employees; no data in RandomList1. See attachment for latest sample.

Ahh, one more to change.

Please Login or Register  to view this content.

Jindon,
Your final revision definitively does the final results I was looking for. Thank you very much for all your hard work and dedication to Excelforum!