Forum Discussion
Consolidating all excel files within a unique folder
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)?
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.
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.