Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving data from one spreadsheet to another
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving data from one spreadsheet to another
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 On Mon, 23 Mar 2009 14:34:01 -0700, Bishop wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving data from one spreadsheet to another
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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving data from one spreadsheet to another
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" wrote: 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 On Mon, 23 Mar 2009 14:34:01 -0700, Bishop wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving data from one spreadsheet to another
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 On Tue, 24 Mar 2009 06:25:02 -0700, Bishop wrote: 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" wrote: 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 On Mon, 23 Mar 2009 14:34:01 -0700, Bishop wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving Data between sheets in the same workbook and moving data between Workbooks. | Excel Worksheet Functions | |||
moving on a spreadsheet with arrows | Excel Discussion (Misc queries) | |||
Matching and Moving Data From One Spreadsheet to Another? | Excel Discussion (Misc queries) | |||
Matching and Moving Data From One Spreadsheet to Another? | Excel Discussion (Misc queries) | |||
Moving Around a Blank Spreadsheet | Excel Worksheet Functions |