How to copy and paste matched rows from one sheet to below exact matched rows in another sheet with name?

Ranv7

New Member
Joined
Feb 27, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi,

Below is the data in sheet1 and sheet2. I have to copy the row matching E & G value in sheet2 and insert the row at the end of the sheet2 with name.
Please help me updating the above program. Thanks

Sheet1
EGName
2502,52314123
4002,52181383
4061540693
6562243424
6561540844
8062634244
10061532191
20061540498
20062369931
30061894136
30062251092
35061925322
40061896446
50061532062
60061859595
60062103167
10102164531
20101532196
25101872991
25101856090
25101540499
25101532197
32102187521
32102230111
32101926699
40102172813
40102172754
40101124757
40102186717
40101540666
40102186720
50101868335
50102502363

Sheet2
ABCDEFGName
16013014465142,5-
19015018480162,5-
210170184100162,5-
240200188125182,5-
265225188150182,5-
320280188200202,5-
3753351812250222,5-
4403952212300222,5-
4904452212350222,5-
5404952216400222,5-
5955502216450242,5-
6456002220500242,5-
7557052620600302,5-
8608102624700402,5-
9759203024800442,5-
107510203024900482,5-
1175112030281000522,5-
1375132030321200502,5-
755011410126-
805511415126-
906511420146-
1007511425146-
1209014432146-
13010014440146-
14011014450146-
16013014465146-
19015018480166-
210170184100166-
240200188125186-
265225188150186-
320280188200206-
3753351812250226-
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
The below one helps me if it is only one column to match.

Sub copy_paste_matched_rows()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim u1 As Double

Application.ScreenUpdating = False

Set sh1 = Sheets("sheet1")
Set sh2 = Sheets("sheet2")

u1 = sh1.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To u1
Set b = sh2.Columns("A").Find(sh1.Cells(i, "A"), lookat:=xlWhole, LookIn:=xlValues)
If Not b Is Nothing Then
sh1.Rows(1).Copy
sh2.Rows(b.Row + 1).Insert Shift:=xlDown
sh1.Rows(i).Copy
sh2.Rows(b.Row + 2).Insert Shift:=xlDown
End If
Next

Application.ScreenUpdating = True

MsgBox "End"
End Sub
 
Upvote 0

Forum statistics

Threads
1,215,052
Messages
6,122,878
Members
449,097
Latest member
dbomb1414

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
Back
Top