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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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:
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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
Back
Top