How to convert data from one long row into multiple rows of 14 columns

[JS011]

Hello,
I have a large data set that I need to reformat for use with another software program. I am working with excel 2010 in windows 7. My data are currently in long rows (from B to CCA, that is 2,106 columns of data). I need to pull out every 13 consecutive cells and make them their own row (e.g. B1:N1 > B1:N1, O1:AA1 > B2:N2, AB1:AN1 > B3:N3). I have to do this for 158 rows of data. Thus, in the end I should have a 13 x 25,596 spreadsheet (i.e. 13 columns and (2,106/13)*158 rows). I have read several forum posts and know that the "offset" function will help me do this, but I cannot seem to figure out how to make it work. I have followed several examples, but cannot get them to work for my data. Can anyone help? If someone can explain how I can even do this with one row of data (not all 158) that would be great and then I can just manually repeat the processes 158 times (sounds fun...).

Thank you

 

[GermanPath]

Hello.

Make a copy of your workbook, close all other workbooks and try this:

Sub range2col_rows()
Dim i As Integer
Dim j As Integer
i = 2
For j = 15 To 2107 Step 13
    ActiveSheet.Range(Cells(1, j), Cells(i, (j + 12))).Copy Destination:=ActiveSheet.Range("B" & i)
    ActiveSheet.Range(Cells(1, j), Cells(1, (j + 12))).ClearContents
    i = i + 1
Next
MsgBox "You have now " & (i - 1) & " rows with 13 filled columns."
End Sub

 

[GermanPath]

And this should do the job for all 158 lines of long rows.

Make a copy of your workbook.
Name the worksheet where the long rows are => Source
Name another blank sheet => Target

runtime take a while. So left your PC alone for a while and come back when macro is done

Sub range2col_rows()
Dim n As Integer
Dim i As Integer
Dim j As Integer
i = 1
For n = 1 To ActiveWorkbook.Sheets("Source").Cells(ActiveWorkbook.Sheets("Source").Rows.Count, "B").End(xlUp).Row
    For j = 15 To 2107 Step 13
        ActiveWorkbook.Sheets("Source").Range(Cells(n, j), Cells(n, (j + 12))).Copy Destination:=ActiveWorkbook.Sheets("Target").Range("B" & i)
        ThisWorkbook.Sheets("Source").Range(Cells(n, j), Cells(n, (j + 12))).ClearContents
        i = i + 1
    
    Next
Next
MsgBox "You have now " & (i - 1) & " rows with 13 filled columns from " & n & " datalines."
End Sub

 

[JS011]

Thank you so much!