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]
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,729
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,653
Messages
5,838,612
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top