Error when extracting information from multiple workbooks

Hello,

I'm using a macro which extracts information from specific cells across all the workbooks in a specified location. It then outputs this information into a separate spreadsheet row by row for each spreadsheet.

The problem I have is that only extracts the information from workbooks that I have opened. If for example I copy a workbook across and don't open it then run the macro it won't extract the information from that workbook (any workbooks I have previously opened will work fine), it just gives me #NAME or #REF errors in the cells it should be placing the information.

Is it something to do with the security on a file ? I am a little perplexed.

The macro I am using is below

Thanks

Please Login or Register  to view this content.

I've tried the code and it only extracts the information from the first spreadsheet in the directory. After this it stops running irrelevant of how many other files are in the directory.

I've just tried debugging the code and when you step into the code and take it line by line, it is cycling through each file correctly as I can see the variable being changed. However the output is still always only the first spreadsheet despite it stepping through each file correctly.

It fails on

Please Login or Register  to view this content.

although I'm not sure why.