Automated Copy and Paste Using VBA

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

Thanks for your offer. Much appreciated. However, I tinkered with it for a few hours and got it working. I hope you didn't spend any time on it.

Thanks again,

Aaron