copy range from multiple sheets and paste to one sheet

Sorry I forgot to Code:

Please Login or Register  to view this content.

Thanks for your help!

I just saw that the range is wrong... that will be E34:I34.

I do not know where to copy the range to? You need to have a sheet called new sheet, or change the Set ms = Sheets("New sheet") in to your choice.

Please Login or Register  to view this content.

The sheet the range has to be copied to is a blank new sheet called sheet1, the ranges should appear in a downwards order starting in row 2 column B to F. There are about 200 sheets, so we will have a set of about 200 lines of data in sheet1 afterwards.

Not clear yet.
E34:I34 is not 5 row range and B-F is a 5 length range

Please Login or Register  to view this content.

Sorry for mixing the terms up!!!

The cells that have to be copied are E34 F34 G34 H34 I34 on about 200 sheets.
These ~200 I want to have on Sheet1, which is empty before.
They should appear listed starting with B2 B3 B4 B5 B6 and continuing downwards.

Have you tried the above code? It does exactly

I work without any Error. There is one Problem left though.
The Code pastes in the same cells every time. It does not create a list.
After running the code, there is only one line of data, which is the one from the last sheet.

It should have said: "IT WORKS without any error." :D

Not, it does not. It copies in to next empty row. I have tested it. Could you please attach your sample, so that I can see the format of your sheet?

I figured out my mistake, it works now.
Thanks so much for your help!!!

David,
You are welcome!
Could you please now close (Mark) this thread as solved? Go in to the top right-hand side of this page, choose "Thread Tools" from the menu, then select "solved" from the drop down menu.

Originally Posted by David_Hiss

I figured out my mistake, it works now.
Thanks so much for your help!!!

hai david,
you could have shown what was your mistake so that others could learn.