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

Report •

#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")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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
✔ Best Answer
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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions
See More ↓

#4
July 31, 2016 at 17:37:08
DerbyDad this worked and is a better solution than option 1,

2 requests:
1. the macro works with visible cells only, my totals weren't adding up and then I realized i had a filter on and so the formulas did not copy to the filtered out cells; can we force it to copy to all cells even if they are filtered out?

2. Is there a way to convert the formula to it's value when completed instead of leaving the formula in the cell?


Report •
Start a discussion

Ask Question