Sub MyCopyMacro()
Dim lr As Long
Dim sg As Long
Dim lg As Long
Dim i As Long
Dim lastLet
Dim lt As String
Dim fc As Long
Dim r As Long
Application.ScreenUpdating = False
' Set array of letters, specifically the last letter in each grouping
lastLet = Array("G", "L", "R", "Z")
' Find last row in column CA with data
lr = Cells(Rows.Count, "CA").End(xlUp).Row
' Sort original data range
Range("CA8:CG" & lr).Sort Key1:=Range("CA8"), Order1:=xlAscending, Key2:=Range("CB8") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
' Initialize variables
i = 0 'initial group
sg = 9 'starting row of data grouping
fc = 98 'first column to paste to
' Loop through data
lt = lastLet(i)
For r = 9 To lr
' See if names falls outside the range
If Left(Cells(r, "CA"), 1) > lt Then
' Set last row for group
lg = r - 1
' Copy data to new range
Range(Cells(sg, "CA"), Cells(lg, "CG")).Copy Cells(9, fc + (i * 8))
' Increment group
i = i + 1
' Get new group ending letter
lt = lastLet(i)
' Set new starting number
sg = r
End If
Next r
' Copy last group
Range(Cells(sg, "CA"), Cells(lr, "CG")).Copy Cells(9, fc + (i * 8))
Application.ScreenUpdating = True
MsgBox "Complete!"
End Sub