Step 1. Save each worksheet into an individual file. You will need Developer tab enabled/visible to use the code below - File/Options/Customize Ribbon/Check ‘Developer’ tab on the right.
thanks to http://www.accountingweb.co.uk/anyanswers/macro-exporting-50-worksheets-csv-one-pass for this code.
Note: files will be generated either under My documents, if the document was unsaved, or within the same path as the document itself, if saved.
Step 2. Manually copy all generated files into some folder.
Step 3. Run Powershell, navigate to that folder (i.e. cd) and run the following command to merge all files into one:
Here credit goes to http://dev.woganmay.com/2011/12/27/merge-multiple-files-using-powershell/ (resource is no longer available, but I will keep it for records).
Step 4. Open the resulting CSV file, and save as XLS if you need to.