Grab specific rows from all worksheets and place into single column

Hello all, I have a bit of a strange request.

I have 50 worksheets in my Excel File.

I am wondering if it's possible to create a new worksheet, and then automatically grab rows 1-50 from each worksheet (column a), and place them in a single column.

My new worksheet would then have 2,500 rows in column a (rows 1-50 from each worksheet).

My worksheets have unique names and it would be quite time consuming to specify each one (in a formula for example) - so ideally I'd like to just move sequentially through worksheets until the end.

I'd also like to repeat the process with rows 51-100 (on a new sheet) - so being able to specify the row numbers would be great.

Thanks in advance for any suggestions.

Thanks so much for the prompt reply. I'm getting Run-time error '1004': Application-defined or object-defined error.

Do I need to hard-code any actual names into the function or should it just run on the new worksheet?

Thanks

Hi there, this is working great thank you.

Just one thing. On the new worksheet - it starts the collection from Row 51 - rather than from Row 1 - not a big deal I can just scroll down to grab them.

Also - if I wanted to change the Row Selection for each worksheet - can I just adjust the values in the alert that pops up - or should I adjust the code manually, eg:

Please Login or Register  to view this content.