Top ten values

[Tom22]

Hi All,

I am looking for code which gives me output from attached file.


İ want to get top 8 salary output from sheet2 and paste it in Sheet3

This file is sample file...actual file has more then 70k rows

Thanks in advance

 

[Chihiro]

Use macro recorder.

Sort Sheet2 based on Sal, descending. Copy first 8 rows of data into Sheet3.

This will give you basis of your code.

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1:C19").Select
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add2 Key:=Range("C2:C19") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range("A1:C19")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1:C9").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A1").Select
    ActiveSheet.Paste
End Sub

Clean it up a bit, and make it dynamic range.

Sub Top8()
Dim lRow As Long
    With Worksheets("Sheet2")
        lRow = .Cells(Rows.Count, "C").End(xlUp).Row
        .Sort.SortFields.Clear
        .Sort.SortFields.Add2 Key:=.Range("C2:C" & lRow) _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With .Sort
            .SetRange Range("A1:C" & lRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        .Range("A1:C9").Copy Sheets("Sheet3").Range("A1")
    End With
End Sub

There are other ways, but this will probably be easiest for you to maintain and change as needed.

 

[Tom22]

Thanks Chihiro....it is working