# Solved Macro to copy data from one sheet to another based on match

July 27, 2016 at 17:52:39
Specs: Windows 7
 I have an excel file that has a sheet with pipeline information on loans (sheet name is "Pipeline"); there is a unique identifier on column D (loan number) in that sheet. In that same excel file but a different sheet called "Projections" that same unique identifier (loan number) is on column "D", there are 4 other columns with data that I would like to copy over to the Pipeline sheet from the Projections sheet based on the loan number match.I would like the macro to match the loan # on column D (range is D2:D600) from Pipeline sheet to column D in Projection sheet (same range) and if found I would like to copy the contents of columns I, J, K and L from the Projections sheet to the same columns in the Pipeline sheet for each loan number that matches.I honestly don't know where to start, I usually use the macro recorder and tweak from there but the macro recorder doesn't really help you create the logic, can anyone help me?

See More: Macro to copy data from one sheet to another based on match

#1
July 27, 2016 at 18:30:29
 Why don't you just use VLOOKUP?The basic formula for Pipeline!I2 would be as follows. Drag this down as far as required.=VLOOKUP(\$D2,Projections!\$D\$2:\$L\$600,6,0)For Pipeline!J2 use =VLOOKUP(\$D2,Projections!\$D\$3:\$L\$600,7,0)To expand on this a bit, you could put this in Pipeline!I2 and drag it both down and across to Column L.=VLOOKUP(\$D2,Projections!\$D\$2:\$L\$600,COLUMN()-3,0)Finally, to eliminate the #N/A error that will occur if a value isn't found on the Projections sheet, put this in Pipeline!I2 and drag it down and across.=IFNA(VLOOKUP(\$D2,Projections!\$D\$2:\$L\$600,COLUMN()-3,0),"Not Found")message edited by DerbyDad03

Report •

#2
July 28, 2016 at 09:45:16
 I overwrite the pipeline data everyday and would have to be mindful of these columns. Once I hand this off to my admin I am sure there will be mistakes in overwriting the formula so I thought VBA code would take out the human error out if it.

Report •

#3
July 28, 2016 at 11:56:34
 I see your point.Two options, both of which eliminate to possibility of human error:1 - Write multiple lines of code to find and copy the data.2 - Write a single line of code to insert the VLOOKUP formula.```Sub InsertVLOOKUP_ISNA() Sheets("Pipeline").Range("I2:L600").Formula = _ "=IF(ISNA(VLOOKUP(\$D2,Projections!\$D\$2:\$L\$600,COLUMN()-3,0)),""Not Found""," & _ "VLOOKUP(\$D2,Projections!\$D\$2:\$L\$600,COLUMN()-3,0))" End Sub```You may have noticed that I switched from IFNA to IF(ISNA). That's because I forgot that IFNA is not backward compatible to versions of Excel before 2013.Here is the IFNA code which will work in Excel 2013 and beyond.```Sub InsertVLOOKUP_IFNA() Sheets("Pipeline").Range("I2:L600").Formula = _ "=IFNA(VLOOKUP(\$D2,Projections!\$D\$2:\$L\$600,COLUMN()-3,0),""Not Found"")" End Sub```message edited by DerbyDad03