Copy ranges from multiple sheets and paste them in another workbook

I got a worksheet which has a button that the user can click. When he does it, I want to copy the range A10 down from 'Sheet1' until it hits a blank cell and paste it starting at B24 in a new workbook which will be opened when the user clicks the button. After this, I want to copy the range A10 down from 'Sheet2' until it hits a blank cell and paste it starting at the first blank cell after B24 in the destination workbook and so on until 'Sheet5'.

So we got:

- Source workbook with 5 sheets
- Destination workbook with 1 sheet
- Values to be copied always start at A10 in each one of the 5 sheets.
- Values must be copied until they hit a blank cell
- First range pasting in the destination workbook starts at B24
- Second range pasting in the destination workbook should start at the first blank cell after B24
- Special paste (values only)

Sample:

Source Workbook - Sheet1

A10	Blue
A11	Red
A12	Green
A13	Orange

Source Workbook - Sheet2

A10	Purple
A11	Yellow
A12	Black

Destination Workbook - Sheet1

B24	Blue
B25	Red
B26	Green
B27	Orange
B28	Purple
B29	Yellow
B30	Black

And so on til Sheet5.

I have this code which works for one sheet only. How can I make it work for 5 sheets?

Please Login or Register  to view this content.

Any help is very appreciated. Thank you so much.

Please Login or Register  to view this content.

Hi, Bernie. Thanks for your reply.

Working fine!