Match two columns and copy specific cells

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I know this has been asked many times over, but I am at a stumbling point and haven't been ale to get past so any help would be appreciated.

The Problem:
I'm comparing the values of column "N" sheet2 to those of column "R" sheet1. If a matching value is found I need to copy/paste values of specific cells from sheet2 to sheet1.

Example: Sheet2, row 50 matches sheet1 row 10
copy sheet2 cell (row50, "BC") to sheet1 cell (Row 10, "K"), etc.

This is the code I'm trying to use but it errors out copying the cell data..

Private Sub Copy_Data1()

Dim Sht1Rng As Range
Dim Sht2Rng As Range

'compares the ID cell in both worksheets to each other
Set Sht1Rng = Worksheets("Sheet2").Range("N2", Worksheets("Sheet2").Range("C65536").End(xlUp))
Set Sht2Rng = Worksheets("Sheet1").Range("R2", Worksheets("Sheet1").Range("A65536").End(xlUp))

For Each c In Sht1Rng
Set d = Sht2Rng.Find(c.Value, LookIn:=xlValues)
'If same value found in col R of Sht2ng sheet then copy
If Not d Is Nothing Then
Cells(c, "BP").Copy Worksheets("Sheet1").Cells(d, "I")
Cells(c, "BA").Copy Worksheets("sheet1").Cells(d, "AT")

End If
Next c
End Sub

Thanks again

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Why dont you use simply a VLOOKUP?
4
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month