# Excel - Comparing cell A1 to entire A column in Sheet 2

## Issue

*I have been trying to compare sheet1 A2 to sheet2 A2 through A500 and if it exists somewhere in sheet2's a col then copy that entire row to a new sheet.
I have experimented with many bits of macro code from others but so far no real success.*

*Figuring out how to say that in code is a bit difficult for me. I'm struggling to get the hang of this.*

**sub compare()
Dim LastRow_1 As Integer
Dim LastCol_1 As Integer
Dim Data_1 As range
Dim LastRow_2 As Integer
Dim LastCol_2 As Integer
Dim Data_2 As range**

**Dim Sh_1 As Worksheet
Dim Sh_2 As Worksheet**

**Dim X As Long
Dim Y As Long
Dim C_1 As range
Dim C_2 As range**

**Set Sh_1 = ActiveWorkbook.Sheets("Master")
Set Sh_2 = ActiveWorkbook.Sheets("Inventory")**

**LastRow_1 = Sh_1.range("A5000").End(xlUp).Row
LastCol_1 = Sh_1.range("A5000").End(xlToLeft).Column
Set Data_1 = Sh_1.range("A2").Resize(LastRow_1, LastCol_1)**

**LastRow_2 = Sh_2.range("A5000").End(xlUp).Row
LastCol_2 = Sh_2.range("A5000").End(xlToLeft).Column
Set Data_2 = Sh_2.range("A2").Resize(LastRow_2, LastCol_2)**

**For Each C_1 In Data_1
For Each C_2 In Data_2
If C_2 = C_1 Then
'found a cell on sheet2 that matched cell in sheet1
'now do what you need to do
Data_2.EntireRow.Copy Destination:=Worksheets("New_Master").range("A5000").End(xlUp).Offset(1, 0)**

**End If**

**Next C_2
Next C_1
end sub**

*When I run this it just copies everything to the sheet called New_Master, including the non-matches.*

*What am I missing? It has to be obvious, that's usually the case.*

## Solution

You just have to write:

Data_2.EntireRow.Copy

You copy the whole range "Data_2", which corresponds to the column A of the sheet "Inventory"

If you want to copy only the rows in Data_2 that match with Data_1, do write

C_2.EntireRow.Copy

That's it!

## Note

Thanks to Ivan-hoe for this tip on the forum.

- How to compare one cell with entire column in excel
- Compare one cell to a column in excel - Best answers
- Excel compare one cell to a column - Best answers
- Compare the words in 2 columns in Excel ✓ - Forum - Excel
- Compare two cells and return value in excel ✓ - Forum - Excel
- Compare cells of two columns excel 2007 ✓ - Forum - Excel
- Compare cell against multiple columns ✓ - Forum - Excel
- Compare two columns in excel for partial matches - How-To - Excel