Match & index to lookup data from multiple worksheets and summarize on one worhsheet

I have daily stock data for about two years.

The stock data is in 556 files but is arranged in the same way (note that about 5 additional stocks have been listed over this period). I want to summarize the movements in stock prices on one worksheet by picking them from the 556 files using match and index with my list of stock codes (named "MatchRange"). I've put the file names of the 556 files on row 2 of the summary worksheet.

I've used the code below but isn't working as it should. It just opens the files and closes them. I've gone through it and run it line by line and the main issue is the match function which doesn't seem to work when the MatchRange contains non-numeric characters. Does anyone have a work around? I'd also appreciate tips on making the code more efficient and less error prone.

Please Login or Register  to view this content.

I'm new to VBA and just learning my way around it. I pieced together code collected from different websites to come up with the code above.

I've attached three Excel files, the Master (SummaryWB) and two sample workbooks from which to copy the required data.
Master.xlsm
2013Apr22.xls
2013Apr23.xls