• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Top ten values

Tom22

Member
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
 

Attachments

  • Veru.xlsm
    20 KB · Views: 4
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.
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.
Code:
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.
 
Back
Top