# Comparing/Moving data between 2 sheets

#### thegoldphish

##### New Member
I have a workbook with 2 sheets.

Sheet 1 has numerous rows with either a number or nothing in row H.
Example:

H
55655

45666
456456

44455
55336

34553

Sheet 2 has numerous rows with this same number in row E (not the same row numbers as sheet 1).
Example:

E
34553
45666
55655
44455
55336
456456

Now here is the hard part. I want to loop through all the numbers on sheet 1 to find which row they are on on sheet 2. Then I want to copy the cell 5 spaces to the right of wherever that number is on sheet 2 and paste it to the right of the place the number is on sheet 1.
Example:

Code:
``````Sheet 1                                Sheet 2

H    I                           E   F  G  H  I   J
55655  Num <--paste here        55655  x  x  x  x  Num <--copy this``````
All of the numbers in the H column(sheet1) will be on the E column(sheet2). Is this possible?

Last edited:

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
thegoldphish,

First, sorry about the code format....
I need to re-find the html editor from this site again.

The following code will work.
It actually could be cleaner, but I exaggerated it to let you see the whole process.

Sub CompareSheets()
Dim a As Integer, b As Integer, c As String
For a = 1 To Sheets("Sheet1").Range("H65536").End(xlUp).Row
For b = 1 To Sheets("Sheet2").Range("E65536").End(xlUp).Row
If Sheets("Sheet1").Range("H" & a).Value = Sheets("Sheet2").Range("E" & b).Value Then
Sheets("Sheet1").Range("I" & a).Value = Sheets("Sheet2").Range("J" & b).Value
End If
Next b
Next a
End Sub

Gary

Replies
0
Views
395
Replies
1
Views
525
Replies
13
Views
621
Replies
3
Views
1K
Replies
7
Views
2K

Threads
1,196,209
Messages
6,014,022
Members
441,801
Latest member
Aneurysm

### 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

### 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