Auto populate sheet with information from another sheet

[Franky2000]

[FONT=&quot]Hello,[/FONT]
[FONT=&quot]I have a sheet that has a number of three column groupings. In each --[/FONT]
[FONT=&quot]The first column is people's names.[/FONT]
[FONT=&quot]The second column is a range of numbers that also includes N/A (i.e. N/A, 1, 2, 3, 4... 20)[/FONT]
[FONT=&quot]The third column has another number.[/FONT]
[FONT=&quot]I have another sheet that has two columns: Name and Number - I want to auto populate that sheet, with data (the name and corresponding number) from two columns of the first sheet.[/FONT]
[FONT=&quot]I also want to ignore any row that has the "Number" column text as N/A (in the first sheet) and only populate the rows that are 1-20 (into the second sheet.)[/FONT]
[FONT=&quot]There must be a way to do this, but after hours of bad internet searching, I'm clueless. Any ideas?[/FONT]

 

[mumps]

This macro assumes you have headers in row 1 and the data starts in row 2. Change the sheet names (in red) to suit your needs.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("[COLOR="#FF0000"]Sheet1[/COLOR]").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With Sheets("[COLOR="#FF0000"]Sheet1[/COLOR]")
        .Range("A1:C" & LastRow).AutoFilter Field:=2, Criteria1:="<=20"
        .Range("A2:B" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("[COLOR="#FF0000"]Sheet2[/COLOR]").Cells(Sheets("[COLOR="#FF0000"]Sheet2[/COLOR]").Rows.Count, "A").End(xlUp).Offset(1, 0)
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub