VBA to combine results from multiple workbooks to one spreadsheet - loop/copy not working

Hi all,

Here's the scenario. In a directory I have 200 or so xlsm files, all of which contain a sheet called "Results". I have a "masterlog" workbook also with a sheet called "log" which is to contain all the data in the "results" sheets of the other workbooks.

I've obtained a macro to loop through all the xlsm files, copy the data and paste to the Log worksheet. However what I'm finding is that it is copying the same libe over and over again.

The code below is not by any means ideal and is written this way in an atempt to find where the problem was occuring but I can't seem to find it.

Any help on this much appreciated - my boss is giving me the evils.

Please Login or Register  to view this content.

It works!!!!

Thank you - this has been driving me nuts for 3 days now!

Thought that might be the case and removed it but it does the same. I've checked the directory and the files are there and of the correct extension (there are other Excel files as well as documents, pdfs etc in there but don't know if / how / why that would have any effect).

I must be a second or two behind your brainwaves....

Have created a list on another sheet and it is looping through different files - its just copying the original data over and over again.

Ive checked the data in the files im copying from in case and it is as it should be.

At least I know the looping bit works!

Well....
With the mouse hovered over the line below I get an automation error (wkbSource.Sheets("Results") = <Automation error>.

Please Login or Register  to view this content.

Could that have something to do with it?

Tried those changes but not making any difference.

What is weird is that, though the debug shows that different files are being opened, and the list shows the same, I watched the task bar and caught the name of the file - it is opening the same file over and over again, even if the VBA says otherwise.

I've looked over similar codes elsewhere on line and everything seems in order but I'm getting text blind at the moment. I'll have a breather and try again.

Oh I could kiss you right now but I've got a coldsore!

Should have known that after my last post.

Many many thanks again for your efforts in this.