Sep 05 2019 05:02 AM
Sep 05 2019 08:23 AM
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.
Sep 06 2019 01:13 AM
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)?
Sep 06 2019 07:39 AM
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.
Sep 07 2019 06:18 AM
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.