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
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
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.
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
range("Y1:Y" & z & "").Clear'-----------clears the cells with the Match function