Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge data from one sheet to another
Have one workbook with two worksheets (Store Number and Store
Database) Store Database has these headers Store Address City Zip Phone Number There are 3000 different stores with this information on this worksheet Store Number currently has the identical headers. Store Address City Zip Phone Number Here is the difference though and our challenge. We get a list of stores each week that we need to do a match from the Store Database sheet. So if Store number worksheet had 23 in A1 it would retrieve the address, city,zip and phone number from the Store Database sheet that has the same store number. This would happen to various amounts of stores. Meaning sometimes our Store Number worksheet would contain 1000 stores, sometimes more , sometimes less. The store number list is supplied and we just copy it to the Store Number worksheet. Thoughts, Ideas, help <grin Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge data from one sheet to another
Think I got it by using a macro that enters VLOOKUP commands into the
top 2 rows for the 3 columns and then ending the macro and manually do a drag and fill for all the columns with data. Does this sound about right or is there another way. Range("B2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Store Database'!R2C1:R3000C4,2,FALSE)" Range("C2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],'Store Database'!R2C1:R3000C4,3,FALSE)" Range("D2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],'Store Database'!R2C1:R3000C4,4,FALSE)" Range("B2:D2").Select Application.CutCopyMode = False Selection.AutoFill Destination:=Range("B2:D3"), Type:=xlFillDefault Range("B2:D3").Select I then manually do the drag in fill for I cant figure out how to do it automatically for the amount of rows with data will always change. Thanks, On Sat, 01 Mar 2008 18:51:42 -0600, " wrote: Have one workbook with two worksheets (Store Number and Store Database) Store Database has these headers Store Address City Zip Phone Number There are 3000 different stores with this information on this worksheet Store Number currently has the identical headers. Store Address City Zip Phone Number Here is the difference though and our challenge. We get a list of stores each week that we need to do a match from the Store Database sheet. So if Store number worksheet had 23 in A1 it would retrieve the address, city,zip and phone number from the Store Database sheet that has the same store number. This would happen to various amounts of stores. Meaning sometimes our Store Number worksheet would contain 1000 stores, sometimes more , sometimes less. The store number list is supplied and we just copy it to the Store Number worksheet. Thoughts, Ideas, help <grin Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge data from one sheet to another
This is a very simle request. I copied the entire row from the stroe
database to store number worksheet. I assumed the store number was in colmn A in both worksheets. Sub get_address() With Sheets("Store Number") RowCount = 1 Do While .Range("A" & RowCount) < "" StoreNum = .Range("A" & RowCount) With Sheets("Store Database") Set c = .Columns("A:A").Find(what:=StoreNum, _ LookIn:=xlValues, lookat:=xlWhole) End With If Not c Is Nothing Then c.EntireRow.Copy _ Destination:=.Rows(RowCount) End If RowCount = RowCount + 1 Loop End With End Sub " wrote: Have one workbook with two worksheets (Store Number and Store Database) Store Database has these headers Store Address City Zip Phone Number There are 3000 different stores with this information on this worksheet Store Number currently has the identical headers. Store Address City Zip Phone Number Here is the difference though and our challenge. We get a list of stores each week that we need to do a match from the Store Database sheet. So if Store number worksheet had 23 in A1 it would retrieve the address, city,zip and phone number from the Store Database sheet that has the same store number. This would happen to various amounts of stores. Meaning sometimes our Store Number worksheet would contain 1000 stores, sometimes more , sometimes less. The store number list is supplied and we just copy it to the Store Number worksheet. Thoughts, Ideas, help <grin Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge data from one sheet to another
I modified you version
Sub get_address2() With Sheets("Store Number") .Range("B2").FormulaR1C1 = _ "=VLOOKUP(RC[-1],'Store Database '!R2C1:R3000C4,2,FALSE)" .Range("C2").FormulaR1C1 = _ "=VLOOKUP(RC[-2],'Store Database '!R2C1:R3000C4,3,FALSE)" .Range("D2").FormulaR1C1 = _ "=VLOOKUP(RC[-3],'Store Database '!R2C1:R3000C4,4,FALSE)" Lastrow = .Range("A" & Rows.Count).End(xlUp).Row .Range("B2:D2").Copy _ Destination:=Range("B3:D" & Lastrow) End With End Sub "Joel" wrote: This is a very simle request. I copied the entire row from the stroe database to store number worksheet. I assumed the store number was in colmn A in both worksheets. Sub get_address() With Sheets("Store Number") RowCount = 1 Do While .Range("A" & RowCount) < "" StoreNum = .Range("A" & RowCount) With Sheets("Store Database") Set c = .Columns("A:A").Find(what:=StoreNum, _ LookIn:=xlValues, lookat:=xlWhole) End With If Not c Is Nothing Then c.EntireRow.Copy _ Destination:=.Rows(RowCount) End If RowCount = RowCount + 1 Loop End With End Sub " wrote: Have one workbook with two worksheets (Store Number and Store Database) Store Database has these headers Store Address City Zip Phone Number There are 3000 different stores with this information on this worksheet Store Number currently has the identical headers. Store Address City Zip Phone Number Here is the difference though and our challenge. We get a list of stores each week that we need to do a match from the Store Database sheet. So if Store number worksheet had 23 in A1 it would retrieve the address, city,zip and phone number from the Store Database sheet that has the same store number. This would happen to various amounts of stores. Meaning sometimes our Store Number worksheet would contain 1000 stores, sometimes more , sometimes less. The store number list is supplied and we just copy it to the Store Number worksheet. Thoughts, Ideas, help <grin Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge data from one sheet to another
I missed a period in one line of the code
from .Range("B2:D2").Copy _ Destination:=Range("B3:D" & Lastrow) to .Range("B2:D2").Copy _ Destination:=.Range("B3:D" & Lastrow) "Joel" wrote: I modified you version Sub get_address2() With Sheets("Store Number") .Range("B2").FormulaR1C1 = _ "=VLOOKUP(RC[-1],'Store Database '!R2C1:R3000C4,2,FALSE)" .Range("C2").FormulaR1C1 = _ "=VLOOKUP(RC[-2],'Store Database '!R2C1:R3000C4,3,FALSE)" .Range("D2").FormulaR1C1 = _ "=VLOOKUP(RC[-3],'Store Database '!R2C1:R3000C4,4,FALSE)" Lastrow = .Range("A" & Rows.Count).End(xlUp).Row .Range("B2:D2").Copy _ Destination:=Range("B3:D" & Lastrow) End With End Sub "Joel" wrote: This is a very simle request. I copied the entire row from the stroe database to store number worksheet. I assumed the store number was in colmn A in both worksheets. Sub get_address() With Sheets("Store Number") RowCount = 1 Do While .Range("A" & RowCount) < "" StoreNum = .Range("A" & RowCount) With Sheets("Store Database") Set c = .Columns("A:A").Find(what:=StoreNum, _ LookIn:=xlValues, lookat:=xlWhole) End With If Not c Is Nothing Then c.EntireRow.Copy _ Destination:=.Rows(RowCount) End If RowCount = RowCount + 1 Loop End With End Sub " wrote: Have one workbook with two worksheets (Store Number and Store Database) Store Database has these headers Store Address City Zip Phone Number There are 3000 different stores with this information on this worksheet Store Number currently has the identical headers. Store Address City Zip Phone Number Here is the difference though and our challenge. We get a list of stores each week that we need to do a match from the Store Database sheet. So if Store number worksheet had 23 in A1 it would retrieve the address, city,zip and phone number from the Store Database sheet that has the same store number. This would happen to various amounts of stores. Meaning sometimes our Store Number worksheet would contain 1000 stores, sometimes more , sometimes less. The store number list is supplied and we just copy it to the Store Number worksheet. Thoughts, Ideas, help <grin Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge data from one sheet to another
Thanks Joel,
I learned a few things getting some of it on my own but after going through your code, I learned alot more. I liked how you did the ..Range("B2").FormulaR1C1 = instead of how I did. You way is much cleaner. Then the Lastrow part seems so obvious but I had no clue it was that simple. I did get rid of the space after Database but everything worked perfectly! Thanks again Joel!! On Sat, 1 Mar 2008 18:54:01 -0800, Joel wrote: I modified you version Sub get_address2() With Sheets("Store Number") .Range("B2").FormulaR1C1 = _ "=VLOOKUP(RC[-1],'Store Database '!R2C1:R3000C4,2,FALSE)" .Range("C2").FormulaR1C1 = _ "=VLOOKUP(RC[-2],'Store Database '!R2C1:R3000C4,3,FALSE)" .Range("D2").FormulaR1C1 = _ "=VLOOKUP(RC[-3],'Store Database '!R2C1:R3000C4,4,FALSE)" Lastrow = .Range("A" & Rows.Count).End(xlUp).Row .Range("B2:D2").Copy _ Destination:=Range("B3:D" & Lastrow) End With End Sub "Joel" wrote: This is a very simle request. I copied the entire row from the stroe database to store number worksheet. I assumed the store number was in colmn A in both worksheets. Sub get_address() With Sheets("Store Number") RowCount = 1 Do While .Range("A" & RowCount) < "" StoreNum = .Range("A" & RowCount) With Sheets("Store Database") Set c = .Columns("A:A").Find(what:=StoreNum, _ LookIn:=xlValues, lookat:=xlWhole) End With If Not c Is Nothing Then c.EntireRow.Copy _ Destination:=.Rows(RowCount) End If RowCount = RowCount + 1 Loop End With End Sub " wrote: Have one workbook with two worksheets (Store Number and Store Database) Store Database has these headers Store Address City Zip Phone Number There are 3000 different stores with this information on this worksheet Store Number currently has the identical headers. Store Address City Zip Phone Number Here is the difference though and our challenge. We get a list of stores each week that we need to do a match from the Store Database sheet. So if Store number worksheet had 23 in A1 it would retrieve the address, city,zip and phone number from the Store Database sheet that has the same store number. This would happen to various amounts of stores. Meaning sometimes our Store Number worksheet would contain 1000 stores, sometimes more , sometimes less. The store number list is supplied and we just copy it to the Store Number worksheet. Thoughts, Ideas, help <grin Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge data from one sheet to another
VBA find is most efficient than the worksheet function VLOOKUP
.Range("B2").FormulaR1C1 = _ "=VLOOKUP(RC[-1],'Store Database '!R2C1:R3000C4,2,FALSE)" can be replaced with the following .Range("B2").Formula = _ "=VLOOKUP(A2,'Store Database '!B2:D3000,2,FALSE)" If the number of rows change in the Store database SDLastRow = sheets("Store Database").Range("A" & rows.count).end(xlup).row .Range("B2").Formula = _ "=VLOOKUP(A2,'Store Database '!B2:D" & SDLastRow & ",2,FALSE)" " wrote: Thanks Joel, I learned a few things getting some of it on my own but after going through your code, I learned alot more. I liked how you did the ..Range("B2").FormulaR1C1 = instead of how I did. You way is much cleaner. Then the Lastrow part seems so obvious but I had no clue it was that simple. I did get rid of the space after Database but everything worked perfectly! Thanks again Joel!! On Sat, 1 Mar 2008 18:54:01 -0800, Joel wrote: I modified you version Sub get_address2() With Sheets("Store Number") .Range("B2").FormulaR1C1 = _ "=VLOOKUP(RC[-1],'Store Database '!R2C1:R3000C4,2,FALSE)" .Range("C2").FormulaR1C1 = _ "=VLOOKUP(RC[-2],'Store Database '!R2C1:R3000C4,3,FALSE)" .Range("D2").FormulaR1C1 = _ "=VLOOKUP(RC[-3],'Store Database '!R2C1:R3000C4,4,FALSE)" Lastrow = .Range("A" & Rows.Count).End(xlUp).Row .Range("B2:D2").Copy _ Destination:=Range("B3:D" & Lastrow) End With End Sub "Joel" wrote: This is a very simle request. I copied the entire row from the stroe database to store number worksheet. I assumed the store number was in colmn A in both worksheets. Sub get_address() With Sheets("Store Number") RowCount = 1 Do While .Range("A" & RowCount) < "" StoreNum = .Range("A" & RowCount) With Sheets("Store Database") Set c = .Columns("A:A").Find(what:=StoreNum, _ LookIn:=xlValues, lookat:=xlWhole) End With If Not c Is Nothing Then c.EntireRow.Copy _ Destination:=.Rows(RowCount) End If RowCount = RowCount + 1 Loop End With End Sub " wrote: Have one workbook with two worksheets (Store Number and Store Database) Store Database has these headers Store Address City Zip Phone Number There are 3000 different stores with this information on this worksheet Store Number currently has the identical headers. Store Address City Zip Phone Number Here is the difference though and our challenge. We get a list of stores each week that we need to do a match from the Store Database sheet. So if Store number worksheet had 23 in A1 it would retrieve the address, city,zip and phone number from the Store Database sheet that has the same store number. This would happen to various amounts of stores. Meaning sometimes our Store Number worksheet would contain 1000 stores, sometimes more , sometimes less. The store number list is supplied and we just copy it to the Store Number worksheet. Thoughts, Ideas, help <grin Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can you merge data from one sheet to another sheet which has . | Excel Discussion (Misc queries) | |||
How to merge data in Excel - one sheet to template sheet? | Excel Worksheet Functions | |||
Merge data from one sheet to another and print | Excel Programming | |||
I have 100 excel sheets how can I merge all data into one sheet | Excel Worksheet Functions | |||
merge data in different excel sheets of same format in one sheet | Excel Worksheet Functions |