Aqui está uma solução do VBA:
Option Explicit
Private Sub ReArrangeCells()
Dim ws As Worksheet, LastRow As Long
Set ws = Excel.ActiveSheet
LastRow = Range("A65536").End(xlUp).Row
Dim i As Long, j As Long, FromCell As Range, ToCell As Range, sNewCol As String, sNewRow As String
For i = 1 To LastRow
Set FromCell = ws.Range("A" & i) 'the cell we want to move
sNewCol = IIf(i Mod 8 = 0, Chr$(72), Chr$((i Mod 8) + 64))
sNewRow = IIf(i Mod 8 = 0, (i \ 8), (i \ 8) + 1)
Set ToCell = ws.Range(sNewCol & sNewRow) 'the cell we want to copy the data to
FromCell.Copy ToCell
If i <> 1 Then FromCell.Clear
If i Mod 100 = 0 Then DoEvents
Next i
End Sub