Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004

    Red face Unanswered: Append data into one worksheet

    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).

  2. #2
    Join Date
    Jan 2004
    Aberdeen, Scotland

    Re: Append data into one worksheet

    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

    Hope this Helps

    Last edited by DavidCoutts; 03-10-04 at 12:55.

  3. #3
    Join Date
    Mar 2004
    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

  4. #4
    Join Date
    Dec 2003
    San Diego, CA
    Welcome to the Board you two!
    !z = lastrow'--------------------put your last used row here,
    You might want to add a bit to that:
    Dim LastRow As Object
      Set LastRow = Sheets("Sheet1").Range("A65536").End(xlUp).Offset(1,0)
    Hope that helps,


  5. #5
    Join Date
    Mar 2004
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts