I have been getting into all sort of problems using the record function and ive tried modifyin these to help but to no avail.

I have 2 workbooks with large amounts of data. I am basically taking an ID number from column B in wb1 then using Ctrl F in wb2 and after finding a match I am copying the data in Col H, AL, AR, BI and copying them into Col I, K, J, L of wb1 respectively.

I have like 1000 of these to do and I am sure a macro could help me do this faster.

Regards,

A friend in need.

Hi parmdeep and welcome to the forum.

A couple of questions for you -

When you search for the ID number in Wb2, will there only ever be one row of information for that ID, or could there be multiple matches? If there are multiple matches then how should they be handled?

When you search for the ID number in Wb2, are you only searching on one worksheet, or do you search through all the worksheets in that workbook?

hi thanks for the speedy response!, luckily there is only one sheet in wb2,

the id number sometimes has same information twice but its the same. so only the first match needs to be copied.

Hi Parmdeep,

That's good news because in that case it sounds to me like you can do this using formulas, thereby avoiding the more difficult VBA solution. Have you used VLOOKUP() or INDEX() and MATCH() before? I can attach an example if required...

Hope that helps...

Hi Colin,

I havent used these functions before, (didn't even know some of those existed) an example would be extremely helpful.

Thanks,

Parm

I've attached a workbook containing an example of each. The idea would be that you put in the formulas to pull in the values, and then copy+pastespecial values to get them hardcoded.

Because it is all contained in the same workbook, the workbook name is not contained in the formulas. If the lookup table was in another workbook then (for example) the formulas would change from:

=MATCH(C$5,'Lookup Table'!$1:$1,0)
to
=MATCH(C$5,'[Book4]Lookup Table'!$1:$1,0)

Have a look at the two examples and also have a look at the helpfile topics for these functions. Hopefully it should make sense...

Does that help?

cheers colin, seems like vlookup was all i needed i had a small issue with the date being displayed as a numeric value but a quick format cells fixed that issue