This requires your unique team names (from column P in sheet1) to be in
sheet2, Cell B3 and extending to the right with no blanks. It uses that
list to set the filter criteria for each team in that list and transfer the
ranks. It is also written to see you sheet1 data starting in column P, row
2 as you currently show.
Sorry, but recorded code would not be appropriate.


Sub DoRanks()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
Dim rng4 As Range, cell As Range
Dim rng5 As Range
With Worksheets("Sheet2")
Set rng = .Range(.Range("B3"), _
.Range("B3").End(xlToRight))
' clears existing data below team names in
' sheet2
rng.CurrentRegion.Offset(1, 0).ClearContents
End With
With Worksheets("Sheet1")
If Not .AutoFilterMode Then
Set rng1 = .Range(.Range("P2"), _
.Range("P2").End(xlDown))
rng1.AutoFilter
End If
Set rng2 = .AutoFilter.Range
Set rng3 = rng2.Offset(1, 0).Resize(rng2.Rows.Count - 1, 1)
Set rng4 = rng3.Offset(0, -12)
For Each cell In rng
rng2.AutoFilter Field:=1, Criteria1:="=" & cell.Value
Set rng5 = rng2.SpecialCells(xlVisible)
If rng5.Count 1 Then
rng4.Copy Destination:=cell.Offset(1, 0)
End If
Next
.ShowAllData
End With

End Sub


--
Regards,
Tom Ogilvy




hawkeyes2002 wrote in message
...

Carl's reply to my original question neither addressed my question or
even had anything to do with my original problem so I really don't know
why it even got posted in this thread. Since Mr. Ogilvy responded to
Carl's problem in this thread, I'm concerned that my original problem
using Auto Filter in a macro will be overlooked. I don't want to
detract anybody from helping Carl out with his issue but it's probably
better suited for another thread.

Again, if anyone has any suggestions relating to my original post I
would really appreciate it.

Thanks,

Eric


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/