VBA for ranking players across multiple sheets

[phimutau]

I'm looking to rank all the players on each team from each position based on the OVR in column H. I would like to have their respective ranks in Column G. So, all the QB's would be ranked against all the other sheets QB's and so on for each position. I have 228 sheets that represent 228 teams. There are A LOT of players. The above screen grab is an example of a sheet. The positions are QB, RB, WR, TE, OL, DL, LB, DB, K, and P located in Column K. Is this doable?

 

[phimutau]

You don't need to do that, I exclude it from Sheets List at Code
Try this:

Sub Test()
  Dim Lr As Long, i As Long, Sh As Worksheet, e As Long, f As Long, j As Long, Ar() As Variant, R As Double
  On Error Goto Resum1
For Each Sh In Worksheets
if Sh.Name <> "List" Then
  f = 60
  With Sh
  Lr = .Range("H" & Rows.Count).End(xlUp).Row
  ReDim Ar(Lr - 60)
  For i = 60 To Lr
  e = Application.Match("Name", .Range("I" & f & ":I" & Lr), 0)
  Resum2:
  e = e + f - 3
  For j = i To e
  Ar(j - 60) = Application.WorksheetFunction.Rank(.Range("H" & j), .Range("H" & f & ":H" & e))
  Next j
  i = e + 2
  f = e + 3
  Next i
  .Range("G60").Resize(UBound(Ar) + 1) = Application.Transpose(Ar)
  End With
End if
  Next Sh
Resum1:
e = 1
Err.Clear
Resume Resum2
End Sub

I tried this and when I hit run i just got the blue rotating circle. I left it alone for over an hour and it just sat there and rotated. I eventually closed it out.

 

[maabadi]

After Inserting Code at VBA Window, First Test First step of Code With F8 Key at VBA window.
if See This Pop-up Window:

"The macros in this project are disabled. Please refer to the online help or documentation of the host application to determine how to enable macros"

Please Go to File Tab, Option, Trust Center, Trust Center Setting
1. At Macro Setting Section, Enable all Macros
2. At File Block Setting, Uncheck all Marked Items
And Then Press OK.
Try this:

Sub Test()
  Dim Lr As Long, i As Long, Sh As Worksheet, e As Long, f As Long, j As Long, Ar() As Variant, R As Double
  On Error GoTo Resum1
    For Each Sh In Worksheets
        If Sh.Name <> "List" Then
        f = 60
        With Sh
        Lr = .Range("H" & Rows.Count).End(xlUp).Row
        ReDim Ar(Lr - 60)
            For i = 60 To Lr
            e = Application.Match("Name", .Range("I" & f & ":I" & Lr), 0)
            e = e + f - 3
Resum2:
                For j = i To e
                Ar(j - 60) = Application.WorksheetFunction.Rank(.Range("H" & j), .Range("H" & f & ":H" & e))
                Next j
            i = e + 2
            f = e + 3
            Next i
Resum3:
        .Range("G60").Resize(UBound(Ar) + 1) = Application.Transpose(Ar)
        End With
        End If
        Debug.Print Sh.Name
        Next Sh
Resum1:
Debug.Print Err.Number
    If Err.Number = 13 Then
    Err.Clear
    If e = Lr Then Resume Resum3:
    e = Lr
    Resume Resum2
    End If
End Sub

 

[phimutau]

We are getting closer. The code worked, but it only ranks the players on their respective sheet. It does not rank them against the other 227 teams players. In the example below I want know what number Lee Jones is ranked out of 700+ QB's.