I've written a macro to copy the 5th,11th,...,n+6th column of the first row and paste it into as single column on a separate spreadsheet. it would then go back to the original spreadsheet and repeat with the next row and so on.


Sub DataTransfer ()

For iRow = 2 To 49
    Sheets("Sheet11").Select
    NameMaxRows = Cells(Rows.Count, "C").End(xlUp).Row
    ModMaxRows = Cells(Rows.Count, "I").End(xlUp).Row 'This makes sure the new data copied does not overwrite previously copied data on the second spreadsheet
    GradeMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
    IntakeMaxRows = Cells(Rows.Count, "L").End(xlUp).Row
    
    For iCol = 1 To 63
        Sheets("Sheet12").Select
        Cells(iRow, iCol).Copy
        Sheets("Sheet11").Select
        Cells(ModMaxRows + 1, 9).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    i = i + 6
    Next i
   
Next iRow

End Sub
The problem now is, I am only getting data from the last column (i.e. 63). How come none of the data from the previous columns are pasted into the second spreadsheet.