Looking for a code that will save as different names to folder based on data validation

Hello, to put it as simple as possible, I am looking for a code that will select different choices from a drop-down menu and then save a copy of that tab to a folder.

Here is a deeper explanation: There are two sheets in this sample workbook. The first one Sheet 1, contains a list of employee numbers, names, and a dept. code they appear in. It also has a key to the side giving a description of the dept. code. On sheet2, the dropdown menu is in cell B8 and is simply from the list in Sheet1 from the range L2 thru L5. When you select a Dept. Code, it will then bring up a matching dept description in B7 as well as a list of people who reside in that dept. starting in Cells A11:B40. So what I'd like to do is have a macro that will select each of these department codes and then save a copy of the file in a folder on the desktop called "Sample". For the file name, I would like it to be whatever is in B8 on sheet 2 and have B7 in the name. So for example if the first Choice in B8 called "Test1" is selected, the File name I would want saved in the folder would be "Test 1 - 1st Shift" and the next choice "Test2" would have a file saved called "Test 2 - 1st Shift Office" and so on and so forth. I've limited the sample to just 4 departments, but it would just basically need to stop at whatever the last dept. is.

I'm not yet at this kind of proficiency in VBA but I believe this would be some kind of Loop coding? Thank you for your time!

Also just FYI if it matters OneDrive is used.

also posted here: https://www.mrexcel.com/board/thread...ation.1246044/