macro to merge worksheets "select method of range class failed"

Hi all,

I borrowed and modified for my purposes the below code. I want to combine the data from a bunch of worksheets into one worksheet. I have many workbooks that each have a worksheet called "extracteddata". I want to open each workbook, copy the non-blank rows from 'extracteddata" and then paste it in to the next non-blank row of a master worksheet. The code below worked when I tested it to open, copy and paste the data from 2 sample files. But when I increased the test to 6 or so files, the code got stuck at file #3. The error I get is "select method of range class failed". All the files have the same structure, so I cannot figure out why the code works for workbooks 1 and 2; but then gets hung up at workbook #3. A clue may be that when the code gets stuck it is visible that 2 ranges of data from 2 different worksheets on the offending workbook are selected. The data from file #1 and #2 are copied into the master correctly, so clearly the macro worked correctly for those 2 files. Any ideas?

Please Login or Register  to view this content.

Thank you for your suggestion! I did edit the paste code. I got an error in that line running exactly as you wrote it. Also I have inserted application.cutcopymode = false to empty the clipboard automatically.

Please Login or Register  to view this content.

Thanks for the thought. I did check that. Check out the solution proposed by another user. It worked.