you can try this method, but it was easier if you uploaded a sample to see how is data organised... the same code is in test workbook attached..
Code:
Sub createGroups()
'first, we will sort all worksheets
Dim wks As Worksheet, i As Integer
For Each wks In ThisWorkbook.Worksheets
wks.Activate
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("C1:C51"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("B1:C51")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
With ActiveSheet
For i = 1 To 8
.Cells(i, "F").Value = "Group " & i
.Cells(i, 7).Value = .Cells(i, "B").Value
.Cells(i, 8).Value = .Cells(i + 8, "B").Value
.Cells(i, 9).Value = .Cells(i + 16, "B").Value
.Cells(i, 10).Value = .Cells(i + 24, "B").Value
.Cells(i, 11).Value = .Cells(i + 32, "B").Value
.Cells(i, 12).Value = .Cells(i + 40, "B").Value
Next i
End With
Next wks
End Sub