I need a vba macro which will copy data from multiple files in a spreatsheet and paste them into a master file with the same layout.
I need data from a9:g9 down to the greatest row filled out of each file. And to be placed in the master file starting at a2:g2. Not changing and closing each data file it pulls into the master file.

Would like excel to do this automatically when the master file is opened or a button on sheet2. there is only one sheet of data in each file to copy.

Theres loads of threads similar to this, but i keep getting problems on them all.

Any help is much appreciated!