Jeff,
what you can do is to have a Tab called Top 10 and have a button in the
other tab to execute the macro as follow

Sub Top10()
'
' Top10 Macro
'

'
Sheets("Top10").Select
Cells.Select
Selection.ClearContents
Sheets("Sheet1").Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("Top10").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Worksheets("Top10").Sort.SortFields .Clear
ActiveWorkbook.Worksheets("Top10").Sort.SortFields .Add
Key:=Range("G2:G5"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Top10").Sort
.SetRange Range("A2:H5")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
That will create a copy of you actual Data and then will sort in descending
order so you will have your top 10

"Jeff Gross" wrote:

The file will be updated weekly so I was hoping to avoid back-end VBA
programming to keep the sorting up to date when the file is opened and the
data is updated.

Jeff

"Eduardo" wrote:

Hi Jeff,
Why you don't highlight the columns and sort it by column G in that way you
will have your top 10

"Jeff Gross" wrote:

I have two columns of data A3:A72 and G3:G72. The first column has plant
names and the second column has a number of accidents. Below is a sample of
the data:

Plant 1-01 1
Plant 1-02 0
Plant 1-03 0
Plant 1-04 0
Plant 1-05 0
Plant 1-06 2
Plant 1-07 0
Plant 1-08 1
Plant 1-09 0
Plant 1-10 2
Plant 2-01 0
Plant 2-02 0
Plant 2-03 0
Plant 2-04 2
Plant 2-05 0
Plant 2-06 0
Plant 2-07 0
Plant 2-08 1
Plant 2-09 0
Plant 2-10 1

I would like to create a Top 10 list of the highest values in G3:G72 and
then return both the Plant Name and its associated number of accidents in
descending order on another tab.

Several of the plants will have the same number of accidents (e.g. 0 or 1 or
2) in which case I would need all of those plants.

I tried using the following formulas on the different tab but it doesn't
appear to work. The first formula is to give the plant name and is started
in row A1 of the tab called "Sheet1" and the second is to give the number of
accidents:

=INDEX('Current Year Data'!A$3:A$72,MATCH(LARGE('Current Year
Data'!G$3:G$72,ROW(A1)),'Current Year Data'!G$3:G$72,0))

=INDEX('Current Year Data'!G$3:G$72,MATCH(LARGE('Current Year
Data'!$G$3:$G$72,ROW(B1)),'Current Year Data'!G$3:G$72,0))

Any help wold be greatly appreciated.

Jeff