April 2017

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.

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

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

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!

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

- Excel - Comparing cell A1 to entire A column in Sheet 2
- Excel compare one row to entire column
- Excell comparing cell to column
- Compare cell a1 to entire A col in sheet2 [Solved] (Solved)
- Excel - Comparing cells across multiple columns
- Copy data onto sheet only if cell has text [Solved] (Solved)
- Compare cells of two columns excel 2007 (Solved)
- Excel - Comparing columns and returning value