Moving data from one spreadsheet to another

B

Bishop

I have 2 spreadsheets: SheetA and SheetB. SheetA is a list of names. SheetB
is a list of names with specific data about each name. So SheetB Column A
has names, Column B has the age, Column C has the address, Column D has total
sales for the month, and Column E has hair color. I need to write a macro
that will take the first name in SheetA, find that same name in SheetB, copy
the info in Columns B, D, E, and paste that info in Columns B,C,D in SheetA
beside the name, move to the next name in SheetA and repeat the process. How
can I do this?
 
G

Gord Dibben

You don't need a macro.

You just need to use the VLOOKUP function.

In SheetA B1 enter =VLOOKUP(A1,Sheet2!$A$1:$E$100,COLUMN(),FALSE)

Copy across to E1

Select B1:E1 and copy down.

If you do not get an exact match you will get #N/A

To avoid that add the ISNA function like so.

=IF(ISNA(vlookup formula),"",(vlookup formula)

Adjust $E$100 to your range.


Gord Dibben MS Excel MVP
 
B

Bishop

The reason I want a macro is I need to assign it to a button. SheetA and
SheetB changes every week. If I have a macro button I can just run the macro
once I have all the names in SheetA.
 
G

Gord Dibben

Sub Auto_Fill()
Dim Lrow As Long
Sheets("Sheet1").Select
Range("B1:E1").Formula = _
"=IF(ISNA(VLOOKUP($A1,Sheet2!$A$1:$E$100,COLUMN(),FALSE)),""""," & _
"VLOOKUP($A1,Sheet2!$A$1:$E$100,COLUMN(),FALSE))"
Lrow = Range("A" & Rows.Count).End(xlUp).Row
Range("B1:E" & Lrow).FillDown
End Sub

Please note I made a mistake in the formula posted yesterday.

I left out the absolute reference for column A as the lookup value.

Corrected in the above version to $A1



Gord
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top