Match two columns and copy specific cells

Closed
LutzCoyote - Aug 3, 2010 at 02:06 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 4, 2010 at 09:01 AM
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 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 4, 2010 at 09:01 AM
Why dont you use simply a VLOOKUP?
4