To search a specific value and paste its corresponding data

Hi,

One excel sheet has two worksheets "report" and "details"
In details sheet, in column A we have code numbers and in column B we have code names.
In report sheet, in column A we will have code numbers same as in details sheets column A(not all may be present). and some other fields.
My requirement is to write a code which will insert a column B in sheet "report" and search for code numbers in "details" sheet and if code number is found then its corresponding value in Column B of sheet "details" should be copied and pasted in "report" sheet in column B against the correct code number.

I have written the below code not sure where i am going wrong as only few rows are getting pasted properly:

Sub CodeToColumn()
Dim names As Range, name As Range, values As Range, value As Range

Set names = Worksheets("Details").Range("A2:A" & Worksheets("Details").Range("A" & Rows.Count).End(xlUp).Row)
Set values = Worksheets("Report").Range("A2:A" & Worksheets("Report").Range("A" & Rows.Count).End(xlUp).Row)

For Each name In names
For Each value In values
If name.value = value.value Then
Worksheets("Details").Range("B2" & name.Row & ":B" & name.Row).Copy Destination:=Worksheets("Report").Range("B2" & value.Row & ":B" & value.Row)

End If
Next value
Next name
End Sub

For now i am manually creating column B in "report" sheet.

Thanks,
Archies

Hi,

I think the same can be done through VLOOKUP or INDEX+MATCH formula, but if you have any restriction using formula than try below code:

Please Login or Register  to view this content.

Regards,

Thanks. It worked. But what mistake i was making in my code? If i you can help me to figure out that then in future i can such silly mistakes.

Regards,
Archies

Hi,

Revise your code with the one below:

Please Login or Register  to view this content.

Regards,

Figured out my mistake. Thanks