Data Source Spreadsheet, Navigator displays Tabs vs Tables

In my opinion loading pages and cleaning up is the best. As long as every new page has the exact same layout you could load all pages within a group everytime the report loads. If you need a quick and easy fix and setting the new sheets as a table is not something you'll be doing an hour every week for the next 10 years it can be a valid solution. preferably however you'd automate it. 

preferably if its a version control which archive makes it seem like it is you would save those as seperate excell files. In that way you can load in a folder, do the nessecary steps to clean for each file and merge them. This works lovely and is probably your cleanest answer. Im not sure if that is possible within one excell file but im sure its possible across multiple files.

The very very best scenario is avoiding either and getting a dataconnection straight to the source of the excell data. unless this is completely  manual entry in which case you'll still end up doing the exact same.