Merge workbooks in a folder and rename sheets to previous filenames

Hi all

slightly struggling with the following.. would be amazing if anyone could help!!

I have a folders each containing different excel files consistently named "Provider A Product X Month Year", and each contain either one sheet only or the standard Sheet 1, .., Sheet 3

Now I would love to merge all of those workbooks (only the first Sheet in case of multiple sheets in a workbook) into one, and have all sheets to be renamed "Month Year", same as in the respective original filnames


Alternatively, or even better, it would be best to have all files merge into the same worksheet, underneath each other, and have the "Month Year" part of the filename be an extra column. I.e. if I have data in e.g. A1:D50 then the macro would add a new column A and write "Month Year" into each of A1:A50

Any thoughts very much appreciated!

Thanks
Markus