Selected Answer
Hi JC and welcome to the Forum.
If the data is in adjacent columns, like M N and O in your case, then you could use the modified code from my recent answer Additional question to video "Transpose Every X Number of Rows in Excel with a Macro - Advanced Transpose Technique".
In the first attached revised file (in the more modern .xlsm file type), I've removed duplicated Module2 and added new Module3 including this code (changes in bold):
Sub Transpose_N_Rows_of_X_Columns()
' TeachExcel.com
' Make the macro run faster on large data sets.
Application.ScreenUpdating = False
' Do something with the user-selected cells/range.
xRow = Selection.Rows.Count
xCol = Selection.Column
xWidth = Selection.Columns.Count
' The row that the Transposed data will be put into.
nextRow = 1
' Hard-code Column header example
'Range("C1").Value = "Column Header"
' How many rows to Transpose.
StepValue = InputBox("How many rows of " & xWidth & " columns should be grouped together?")
' Loop through the user-selected data using a step value.
For i = 2 To xRow Step StepValue
' Copy the data, using the step value to determine the size of
' the copied range.
Cells(i, xCol).Resize(StepValue, xWidth).Copy
'Transpose the data.
Cells(1, xCol).Offset(nextRow, xWidth).PasteSpecial Paste:=xlPasteAll, Transpose:=True
' Increment the nextRow value so the copied data goes onto
' a new line.
nextRow = nextRow + xWidth
Next i
' Remove the "copy lines" from the Transposed data.
Application.CutCopyMode = False
' Make Excel function as expected after the macro is finished.
Application.ScreenUpdating = True
End Sub
The code will run on any sheet but to demonstate it, I've copied data from Sheet2 columns M, N and O to a new worksheet "Demo sheet" (with a empty first row* since Don's original tutorial assumes a header row). The code is behind the button "Transpose selection by columns". If you click that and answer 1 to the question, the data will be grouped as transposed groups of 3x1.
* Note if you don't have an empty header row before running the macro, the first 1x3 group will be missed since the code statrts on the second row, owing to this line:
For i = 2 To xRow Step StepValue
You could change the 2 to 1 to fix that.
I added a second file as .xls file type in case you have an old version of Excel (please modify your Forum profile to state which version of Excel you use- this can be important for some answers).
Hope this helps.
