It nearly works not sure what I'm doing wrong....

If I try it with only one excel file it copies the data into the cells and then I get a 'subscript out of range' error.
If I try it on multiple excel files it copies the data into the cells for the first excel file and the stops opening any more excel files and I get the 'subscript out of range' error....