Consolidating all excel files within a unique folder

Copper Contributor
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)
clipboard_image_1.png
 
Folder with 113 files
clipboard_image_3.png
4 Replies

@PaulaSpinola 

I recommend using power query functions instead of writing a VBA script.  

Please follow the menu option Data->Get Data-> From File -> From Folder. 

It will pick all files and then you need to apply the consolidation. 

 

Many thanks @Kodipady 

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)?

@PaulaSpinola 

In principle everything you can do using keyboard , VBA can do it for you. hence  I believe it should be possible to keep same format in a VBA script. i am not an expert on VBA.

 

Using power query, it may not be possible to keep same format. but it provides lot more flexibility to consolidate and transform. 

@Kodipady

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.

clipboard_image_0.png

 

clipboard_image_1.png