Life will be a lot easier if, as you suggest, combine all of the worksheets into a single data source.
Other then cut-and-pasting these in Excel (Excel handles >>1000 rows, but at some point becomes unstable), there are a few ways to do this. In increasing order of complexity:
1. Tableau: Add Data From File
Create an Tableau Extract from the initial Excel file (when you connect to the Excel file, select import all data). Then right-click the data source in the upper left pane > Extract > Add Data From File.
2. Use separate sheets in Excel and Custom SQL in Tableau
If the sheets are in the same workbook, you can use Custom SQL and a UNION ALL statement to "stack" the data into a single data table. Search for UNION ALL and you'll see an example and, if not, reply here and I can sketch it out.
3. Use an Excel macro / Excel federated connection
I haven't done this, but have seen it recommended on the Forums. If you Google for combine multiple files in Excel, you should see a few options. My understanding is that there is a method that allows you to keep the files separate (allowing you to update them independently); hence the term federated---Excel is providing a connection to multiple files.
4. Use a script or third-party tool like Alteryx
Alteryx has a free project edition that can do this. This is probably easier than (3) above, depending on your familiarity with Excel or comfort level with ETL type software. If you want to go this route and hit a roadblock, post a follow-up and someone will likely post a short example.