Apo: The objective of the program is to pull information from named cells in many similar workbooks and place it on a worksheet, without having to open the workbooks and copy/paste the info.
nathansav: You were right to question the formula, my code now works.
For some reason when a filepath contains an appostrophe - ' - then it has to be replaced with two apostrophes - '' [NOT "] - when using the file calling technique employed here. e.g. HMO's becomes HMO''s. Does anyone know the reason for this out of interest??
Note: I am calling the files from a mapped network drive, not a local one on the computer.
Also I had to remove the square brackets and didnt need to include the sheet name. For reference my new code is below:
Bookmarks