Merge data from one sheet to another

[test1]

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

 

[test1]

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("B22").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B23"),
Type:=xlFillDefault
Range("B23").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,

 

[Joel]

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

 

[Joel]

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("B22").Copy _
Destination:=Range("B3" & Lastrow)
End With
End Sub

 

[Joel]

I missed a period in one line of the code
from
.Range("B22").Copy _
Destination:=Range("B3" & Lastrow)
to
.Range("B22").Copy _
Destination:=.Range("B3" & Lastrow)

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("B22").Copy _
Destination:=Range("B3" & Lastrow)
End With
End Sub

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

 

[test1]

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!!

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("B22").Copy _
Destination:=Range("B3" & Lastrow)
End With
End Sub

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

 

[Joel]

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 '!B23000,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" & SDLastRow & ",2,FALSE)"

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!!

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("B22").Copy _
Destination:=Range("B3" & Lastrow)
End With
End Sub

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

:

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