best way to copy data in a new location based on criteria?

[solarissfSF]

hi everyone,

I have a long list of data in excel tab, what I want to do is on another tab, just show the rows that meet certain criteria, sorted by a date column. For example

master list---------------------------------
1/1/20 property taxes $500 $0
2/1/20 property taxes $500 $0
3/1/20 property taxes $0 $500

basically if the 4th column has an amount > 0, then I want that row to show up on another tab... in date order.

so in the above example only this row would show up in a different tab
3/1/20 property taxes $0 $500

What would be the best way to accomplish this? I know I can do a pivot table, but I'm not sure if that is the most efficient way. I see some posts on vlookup/match/index but again, I'm looking for the best way.

Thank You!

 

[kennypete]

The "best" way could spark debate, though the quickest way may be to AdvancedFilter copy the rows that meet the criteria (or in your case criterion, since there is only one):

Sub AdvancedFilterCopy()

    Dim shtData As Worksheet
    Dim shtReport As Worksheet
    Set shtData = ThisWorkbook.Worksheets("Sheet1")
    Set shtReport = ThisWorkbook.Worksheets("Sheet2")
    shtReport.Cells.Delete

    ' Remove any filters
    If shtData.FilterMode = True Then
        shtData.ShowAllData
    End If
   
    ' Get source data
    Dim rngData As Range
    Dim rngCriteria As Range
    Set rngData = shtData.Range("A1").CurrentRegion
   
    ' The criteria range; in this case just a criterion
    Set rngCriteria = shtData.Range("F1:F2")
  
    ' Apply the filter
    rngData.AdvancedFilter Action:=xlFilterCopy _
                , CriteriaRange:=rngCriteria _
                , CopyToRange:=shtReport.Range("A1")

End Sub

Sheet1:

best-way-to-copy-data-in-a-new-location-based-on-criteria.1138060.xlsm
	A	B	C	D	E	F
1	Date	Text	1$	2$		2$
2	1/01/2020	pt	$500	$0		>0
3	2/01/2020	pt	$500	$0
4	3/01/2020	pt	$0	$500
Sheet1

Note I've put the criterion in cells F1:F2 of the "data" sheet (Sheet1), though it could be elsewhere. The "report" is Sheet2, the content of which is deleted each time.

This will be super fast. I tried it on >8,000 rows of data and it's instantaneous.

Output (when using your original data only):