With your multiple sheets from a single file (or even multiple sheets from multiple files) do you want to:
1. union them, and all have the perfectly exact same structure?
2. union them, and they have at least a slight variation? (different order, different names, different data type or length)
3. join them, with a common key field between them all? (one-to-one style)
4. join them, with different keys fields? (one-to-one and/or one-to-many mixed)
5. some mixture of the above options?
6. none of the above, bring in each sheet as a separate stream?
7. something else that I have not accounted for above?
With more details and a sample file that represents your situation, and a mock-up of what you want for a result, we can make a macro and workflow to fit your situation.
For an example of #2, see https://community.alteryx.com/t5/Data-Preparation-Blending/Help-reading-from-multiple-Excel-sheets-w...