Copy paste rows in Excel across worksheets

Solved/Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,
I'm trying to create an excel macro that will copy a sequence of rows from 2 different worksheets and paste them in a third worksheet using the spacing described below.

Currently, the workbook consists of:

(Sheet 1)
Data1a
blank row
blank row
Data2a
blank row
blank row
.
.
.

(Sheet 2)
Data1b
blank row
blank row
Data2b
blank row
blank row

.
.
.
(Sheet 3)
Data1c
blank row
blank row
Data2c
blank row
blank row
.
.
.

The result I am trying to achieve is:
(Sheet1)
Data1a
Data1b
Data1c
Data2a
Data2b
Data2c
.
.
.

Thanks in advance for any advice

Mark

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Copying row at a time does not sound like a good idea on surface. I would suggest you do this

1, Filter the 3 sheets on "non blank"

2. copy the visible row from sheet1 on to the new sheet

3. in the new sheet where data was pasted, mark all the rows in serial number ( 1 to N) and in another column mark it as set A

4. Copy the visible row from sheet2 and paste right after the last row pasted earlier. Mark all these rows as set B and mark the serial number of this set too (1 to N)

5. copy the visible row from sheet3 and paste right after the last row pasted earlier. Mark all these rows as set C and mark the serial number of this set too (1 to N)

6. Now sort the data on new sheet on serial number and secondary sort on set source
Thank you, that worked great.

Mark
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You are welcome Mark. Good luck.