Copy/amalgamating Multiple Ranges

I've been using this code from the ever brilliant Ron de Bruin to copy a range from multiple spreadsheets in a folder:

Please Login or Register  to view this content.

and it's working brilliantly.

My problem is that I now need it to copy across multiple ranges (6 to be exact) which are different sizes. Each range needs copying to a separate sheet.

Currently I'm achieving this by having this code in 6 separate modules and running one after the other, which does the job. The problem is the because of the coding it's opening and closing each spreadsheet 6 times.

Could someone tell me how I'd adjust the above code to enable all 6 ranges to be copied across (including the filename) at once, so that the file only opens once.

My coding is getting better (in fact I've altered this myself to do what I need), but I've tried and tried with this and I'm having no success.

Thanks.

Sorry Arlu.

Each range needs copying to a separate sheet. It doesn't matter which sheets (it can simply be 1 through 6), they just have to be separate.

The above code pulls a single range through (for all excel spreadsheets in a specified folder) and stacks them on sheet 1 with the filename in column A. I need them to stack like that, but I don't mind which sheets each range is copied to.

Thanks for your help.

Sorry for the double post, but I thought I'd explain a bit further.

The code above does what I needed in the beginning, and I adjusted it to serve my purposes (i.e. copying more than one range) by creating new modules and adjusting the range within each one. While this worked, running the process on the folder I had wound up taking 5mins +, as each module opened each spreadsheet in turn and only copied across the specified range.

I thought that amalgamating the code so that all ranges were copied across from each spreadsheet at once, thereby requiring each sheet to only open once, would speed up the process. I just haven't been able to figure out how to do it.

Thanks for the reply snb.

I've added the code and when I run it I'm getting a syntax error on the getobject(c01) line.