Merging all Workbooks in a Folder copies from the first workbook only,but not all

[AB33]

I think this code is written by Ron. I have adjusted some of the lines to suite my needs. The code loops correctly through all files on the folder, but copies only the range from the first book. It loops through the rest of the folder, but does not copy anything. I have range values in most workbooks, but the code does not copy. My books have a mixture of file extenstions, xlsx, xlsm.

Any idea, or suggestion would be much appreciated!

Please Login or Register  to view this content.

[Richard Buttrey]

Try using

Please Login or Register  to view this content.

instead of
DestRange.Value = SourceRange.Value

[AB33]

Richard,

Thank you very much for your help!

Infact, I find out what was the issue. The code copies from the first book from Range(A1-1000) and then it correctly copies from the second book as well, but instead of copying in the next empty row, it copies starting from row 1001. My first book has only 9 rows (Range 1-9), so despite the range specified is 1-1000, I would have expected the next empty to be copied should in row 10, not 1001.

Your code helps me to see all rows up to 1000 have been highlighted when the code pastes.

As I had expected the code to copy in the nexy empty row which should be row 10, but the code was copying in row 1001, hence I did not go down to see the copied range below 1000. When I saw empty rows I thought the code was not working. In fact the code was copying below 1000 which I could not see.

Do you have an idea why the code behaves this way? Usedrange may be a solution.

[AB33]

Richard,
I am really sorry for addressing you with the wrong name. I have now corrected the thread.