I humbly seek the wisdom of you amazing people... I am trying to help a friend who is an accountant and like me, also has no clue in achieving this mail merge feature..

Trying to accomplish:
A word document that would fill mail merge the respective fields, but it would work from either one, several, or all worksheets available.

In the Excel screenshot examples below, I've listed two worksheets business's by their "Code", BBB and FPP, which represents the business's (Bob's Burgers and Frankies Pizza)

In the Word screenshot below, you can see that I have the written and inserted the fields I want populate.

What I'd like to have, upon opening the Word document that is linked to that excel file, is a popup box (VBA script?) which asks... Type in Code(s), seperated by a comma to mail merge, or a "All Codes" mail merge.

That's not going to happen without a lot of effort. A mailmerge can only work with a single data source - in this case a single worksheet. Besides which, it's not apparent what the purpose is behind having different worksheet names in column C when both sheets appear to be in the same workbook.

What is the problem with a having different mailmerge main document for each data source?

The problem is the main data source has well over 50+ worksheets, which would mean you would need to have 50 separate files, which points to the right worksheet for each separate client.. (and counting as more clients come along), this of course can be achieved, creating all those files, but its not ... shall I say, a elegant solution to the problem...

But I completely agree, this is a huge monumental task or some real wizardry VBA script needed, to which I have no idea how to do...