Auto populate sheet with information from another sheet

Franky2000

New Member
Joined
Mar 15, 2019
Messages
1
[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]
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,589
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.
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,353
Messages
5,528,203
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top