I need to create a macro in the attached spreadsheet which when run, will open a range of spreadsheets in a folder called “Z:\Dump\Test_Funds” and pull the corresponding value in the cell to the right of 2 cells containing the words “Fund’s name” and “Fund's NAV (MN EUR)” .
For the purpose of this macro, the spreadsheets begins with opening files starting with the name “2012-0001” and finish upto “2012-1000” but their full file name will include some words after the initial number e.g. “2012-0001 Emerging markets Fund”. The macro should only focus on the files that begin with those numbers with the range specified. Also at present there are only some 50 files in the folder, so some sort of command to loop until it finds all files in that range but end if no more.