I would like to combine data from more than 100 excel files (all in the same folder) into a unique file. The range from each spreadsheet that should be copied into a unique file goes from row A8:K8 until A?:K? where "?" refers to the last row of the respective spreadsheet. Also, the names of the files are not standardised. Would it be possible to write a VBA code that asks excel to call every file within a folder?
Example of a spreadsheet (selected cells should be copied into a new unique file)
This would work but I actually need the colour formatting to be the same as in the original file (as I use it to run a macro afterwards). Is there a way different files within a folder could be combined while keeping the original format (either using power query or VBA)?
When using your suggestion, is there any way that I can tell excel to only consolidate data from row 8 (until the last row in each of the spreadsheets)? I clicked on Combine & Edit (print screen below) but did not find any option to select a range.