How can I append data from several worksheets into one worksheet according to one ID number? Example : I have 2 worksheets, one contains database with fields : Name, Age, Job and address. Worksheet2 contains fields : Name, bodyweight, height, hobbies. Can I automatically append the data in Worksheets 2 by using VBA into Sheet1 according to the field Name (so data in sheet1 will be Name, Age, Job, address, bodyweight, height and hobbies).

Probably the best way of doing this is with using the VLOOKUP function.

you could write VBA to find the range your looking for then add that to the VLOOKUP function.

for the example above the function should read something like

=VLOOKUP($A1,sheet2!$A$1:$D$14,2,FALSE)

where $A1 is the value you are wanting to match i.e. Name
sheet2!$A$1:$D$14 is the total range of data you are looking at
2 is the column position of the data in the Table
and FALSE to imply that you are looking for exactly what is in $A1


Hope this Helps

David

Are there duplicate records for a given name? If not you could use the "=match()" which would return the corresponding row in the second sheet. Then all you need to do is transfer the values.

Option Explicit

Public Sub test()

Dim col As Integer
Dim row As Integer
Dim match As Variant
Dim z As Integer
z = lastrow'--------------------put your last used row here,
range("Y1:Y" & z & "").Formula = "=MATCH($A1,Sheet2!$A$1:$A$" & z & ",0)"'--------------------------"=MATCH" formula, puts it in column Y
col = 1
For row = 1 To z
match = Cells(row, col + 24).Value
If IsError(match) = True Then----' no match returns #N/A so skip
Else: range("B" & row & ":C" & row & "").Value = _
Worksheets("sheet2").range("B" & match & ":C" & match & "").Value _ 'need to append the range to transfere, right now 'set to copy columns B and C

End If

Next row
range("Y1:Y" & z & "").Clear'-----------clears the cells with the Match function

End Sub

Welcome to the Board you two!You might want to add a bit to that:Hope that helps,

Smitty

My bad
for things i've done lastrow() is a function that's called to determine the last used row which is why it wasn't declared.