Hi All,

ScreenCap.JPG

I have a spread sheet (see attached image above) and would like to automate a process where by you can copy all the X data from the I, K, M and O columns in to the Q column. And copy all the Y data from the J, L, N and P columns. I have done the first 3 rows of data by copying each row and transposing it so it displays vertically. I also recored a macro to see what the code looks like when you do this by hand.

For the X data:
Sub Copy_X_Data()
    Range("I2,K2,M2,O2").Select
    Range("O2").Activate
    Selection.Copy
    Range("Q2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

    Range("I3,K3,M3,O3").Select
    Range("O3").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Range("Q6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

    Range("I4,K4,M4,O4").Select
    Range("O4").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Range("Q10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End Sub
For the Y data:
Sub Copy_Y_Data()
    Range("J2,L2,N2,P2").Select
    Range("P2").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Range("R2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

    Range("J3,L3,N3,P3").Select
    Range("P3").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Range("R6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

    Range("J4,L4,N4,P4").Select
    Range("P4").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Range("R10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End Sub
I would like to make this process automatic. That is, have some sort of loop around the above code so that it runs through all the data and copies everything.

Hope that all makes sense.

Best Regards,

Aaron