Copy data from every nth column into another spreadsheet

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.

the line cells(modmaxrows+1,9).pastespecial is what is pasting the data into the column. Both the row (modmaxrow+1) and the column (9) remain unchanged as you loop through the columns from 1 to 63, therefore it will just overwrite each column until you get to 63.

For iRow = 2 To 49

is looping from 2 to 49 in sheet11.

Why do you need to loop again on sheet12?

For iCol = 1 To 63

If you are copying from sheet11 and paste them in sheet12, you need to loop in sheet11.

For e.g

  Sheets("Sheet11").Select
  For iCol = 5 To 63 Step 6

loops from column to 63 every 6th column

Maybe you can simplify this to:

Sub DataTransfer()
Dim iRow, iCol
For iRow = 2 To 49
    For iCol = 1 To 63 Step 6
        Sheets("Sheet11").Cells(Range("I" & Rows.Count).End(xlUp).Row + 1, 9).Value = Sheets("Sheet12").Cells(iRow, iCol).Value
    Next
Next
End Sub

wow worked like a charm! thanks yudlugar(: