Transpose column of data to multiple rows of data, without having to manually 'cut' series

Try this macro - not sure what you want to do with the other data, but this should get you started.

Option Explicit

Sub TestMacro1()
    Dim R As Range
    Dim S As Worksheet
    Dim T As Worksheet

    Set T = ActiveSheet

    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("After").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    
    T.Copy After:=Worksheets(Worksheets.Count)
    Set S = Worksheets(Worksheets.Count)
    S.Name = "After"
    
    With S
        .Range("BT:BZ").ClearContents
        For Each R In .Range(.Range("BR1"), .Cells(.Rows.Count, "BR").End(xlUp))
            If R.Value = "MARKER" Then
                R.Offset(0, 2).Value = R.Offset(-3, 0).Value
                R.Offset(0, 3).Value = R.Offset(-2, 0).Value
                R.Offset(0, 4).Value = R.Offset(-1, 0).Value
                R.Offset(0, 5).Value = R.Offset(1, 0).Value
                R.Offset(0, 6).Value = R.Offset(2, 0).Value
            End If
        Next R
        .Range("BT:BZ").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        .Range("BN:BP").Delete
    End With
    
End Sub