VBA - importing all excel files in subfolders

Hi, i have a bunch of excel files (with only one sheet per file) i need to import into one consolidated spreadsheet - so far i've done it manually but this is a very time consuming and error prone task so i'm turning to you geniouses for some advice.

I have a folder with six sub-folders, which all contain 10 excel files with the same names (as in ten unique names per folder, but it repeats for every folder). So, if a piece of code can do something like running through the folders, imports all the excel files and names them with the appropriate the sub-folder name + the file name, then it would literally save me days of work (i have to do this every two weeks!:banghead:). If something like this exists already, or if anyone has some advice on how to make this I will be forever grateful!

Thanks

did you search this forum ?
you'll find tons of codes.