Copying alternating rows from one sheet to another (Weaving)

masterelaichi

New Member
Joined
Sep 29, 2014
Messages
48
Hi all,

Is there a way to "weave" rows from one excel sheet to another? By weaving, I mean copying alternating excel rows. I have two different workbooks with monthly reports. Both reports have the same number columns

The workbook contains the progress of certain projects in the organisation. The reason behind this is so that I can compare two rows for the same project, i.e data from previous month vs current month

For example, I have two sheets, Source Sheet and Destination Sheet

Source Sheet (previous month) has rows:

1A
1B
1C
1D

Destination Sheet (current month) has rows:

2A
2B
2C
2D
2E
2F
(2A and 2F are new projects added in the current month)

I want a resulting sheet that looks like below:

2A
1A
2B
1B
2C
1C
2D
1D
2E
2F

Is there a way to do this without having to manually copy the rows?

Thanks in advance
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Hi all,

Is there a way to "weave" rows from one excel sheet to another? By weaving, I mean copying alternating excel rows. I have two different workbooks with monthly reports. Both reports have the same number columns

The workbook contains the progress of certain projects in the organisation. The reason behind this is so that I can compare two rows for the same project, i.e data from previous month vs current month

For example, I have two sheets, Source Sheet and Destination Sheet

Source Sheet (previous month) has rows:

1A
1B
1C
1D

Destination Sheet (current month) has rows:

2A
2B
2C
2D
2E
2F
(2A and 2F are new projects added in the current month)

I want a resulting sheet that looks like below:

2A
1A
2B
1B
2C
1C
2D
1D
2E
2F

Is there a way to do this without having to manually copy the rows?

Thanks in advance

Maybe you can work with this to do what you want. As you can see by the comments in the code, There was incomplete information in the OP to write the code for your specific setup, but this is a generic model and can be made to work by filling in things like sheet names where I used index numbers, since the index is subject to change. Also, I used column A to match data from one sheet to another and that may not be the best column to use, but I wouldn't know since that info was not provided in the post.

Code:
Sub copyPrev()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, fn As Range
Set sh1 = Sheets(1) 'Previous Month-Use actual sheet name
Set sh2 = Sheets(2) 'Current Month-Use actual sheet name
lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row
    For i = lr To 2 Step -1  'Start at bottom and work up
        Set fn = sh1.Range("A:A").Find(sh2.Cells(i, 1).Value, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            fn.EntireRow.Copy
            sh2.Rows(i + 1).Insert
        End If
    Next
    Application.CutCopyMode = False
End Sub
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,988
To do this without macros

Put a helper column on Sheet 1 with the formula =ROW(A1)+.2
Put a helper columns on Sheet2 with the formula =ROW(A1)+.1

Copy/PasteSpecial Values those helper columns to turn the formulas into values

So on sheet 1 you should have two columns

1A 1.2
1B 2.2
1C 3.2
..
And on the second
2A 1.1
2B 2.1
...


Then copy both columns from sheet 1. Paste that at the bottom of sheet2, giving

2A 1.1
2B 2.1
2C 3.1
2D 4.1
2E 5.1
2F 6.1
1A 1.2
1B 2.2
1C 3.2
1D 4.2

Then sort those two columns on the values in the helper column.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,163
Messages
5,640,519
Members
417,150
Latest member
cdguinn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top