Moving data from one spreadsheet to another

[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?

 

[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

 

[Ashish Mathur]

Hi,

You do not need a macro for this. Please read up on the VLOOKUP() function
in the Helo menu

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[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.

 

[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