Sub Macro()
Dim i As Double
Dim j As Double
Dim od As Worksheet
Dim nw As Worksheet
Set od = Sheets("Sheet1")
Set nw = Sheets("Sheet2")
For i = 1 To 8
j = od.Application.WorksheetFunction.Match(od.Application.WorksheetFunction.Large(Range("b2:b20"), i), Range("b2:b20"), 0)
nw.Range("a" & i & ":" & "b" & i).Value = od.Range("a" & j + 1 & ":" & "b" & j + 1).Value
Next i
MsgBox " Top 2 are accepted to Department A" & vbNewLine & _
"Next 2 (3,4)are accepted to Department B" & vbNewLine & _
"the 5th best score is accepted to Department C" & vbNewLine & _
"For the last 3 of the top scorers (meaning 6,7,8) Back ups"
End Sub
[/C
[QUOTE="ER_Neha, post: 5580047, member: 464198"]
Hello Panther , Please try this vba code , if it satisfy your requirement. IGNORE - the message box writing, you can manage that I suppose. However, the button for the macro must be created in the sheet where you have your entries, the new sheet (2) will contain the top 8 entries upon clicking the button.[CODE=vba]
Sub Macro()
Dim i As Double
Dim j As Double
Dim od As Worksheet
Dim nw As Worksheet
Set od = Sheets("Sheet1")
Set nw = Sheets("Sheet2")
For i = 1 To 8
j = od.Application.WorksheetFunction.Match(od.Application.WorksheetFunction.Large(Range("b2:b20"), i), Range("b2:b20"), 0)
nw.Range("a" & i & ":" & "b" & i).Value = od.Range("a" & j + 1 & ":" & "b" & j + 1).Value
Next i
MsgBox " Top 2 are accepted to Department A" & vbNewLine & _
"Next 2 (3,4)are accepted to Department B" & vbNewLine & _
"the 5th best score is accepted to Department C" & vbNewLine & _
"For the last 3 of the top scorers (meaning 6,7,8) Back ups"
End Sub