Tente o seguinte, que acabei de criar, ou seja, se você estiver atrás de uma versão de macro em vez da fórmula já sugerida.
Basicamente, eu acho que deveria fazer o que seu código estático faz, mas dentro de um loop - então não há trabalho manual de copiar e colar para fazer. Pode engasgar se a coluna inteira estiver cheia (ela morrerá quando tentar selecionar após a última linha).
Pode ser necessário alterar os locais de início e de colagem (próximo ao topo) para atender aos seus requisitos específicos. No momento, ele copia da coluna B para as colunas C-F.
Dim CopyCell As Range
Dim PasteCell As Range
'starting location (ie, top of the list to copy)
Range("B1").Select
'pasting location (ie, top-left cell to start pasting to)
Set PasteCell = Range("C1")
Do While ActiveCell.Value <> 0
'copy the items in the next four rows
Set CopyCell = ActiveCell.Range("A1")
ActiveCell.Range("A1:A4").Select
Selection.Copy
'move to paste location
PasteCell.Select
'paste with transpose
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
'adjust paste location for next paste
Set PasteCell = PasteCell.Offset(1, 0)
'return to last copied location and and move down five steps
'copying 4 rows but moving 5 is how every 5th row is missed
CopyCell.Select
ActiveCell.Offset(5, 0).Select
Loop