is it possible to automate copy & paste special "transpose"?

[sadams1]

Thank you for taking time to read this potential insanity! I've got a file with more than 50,000 rows of data that is organized in 3 row chunks.

The 1st screenshot shows an example of one 3 row chunk. What I'd like to be able to do is to copy the range "A1:A3" then "paste special > transpose" that range into columns "C1:E1".
This would be a piece of cake to do manually with a managable set of data but what I'm working with is too large to do this like that.

The 2nd screenshot shows two 3 rows chunks with two contiguous ranges in column A copied & pasted into column C, D, & E manually.

My question is is it possible to automate this for a much larger set of data or is this way more than I'm thinking?

 

[dermie_72]

If you select the text and then run this macro it will do it. It will move the transpose 2 columns to the right. If you want it in a different location, consider that in the offset.
I would also recommend setting up either a shortcut key, or adding it to the quick access tool bar

Sub sadams1()
    Selection.Copy
    ActiveCell.Offset(0, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
End Sub

 

[sadams1]

hello Dermie & greatly appreciate this! I run the code & attached is the screenshot of the result.

Is it possible to have it look more like screenshot 2 from original post?

 

[dermie_72]

hello Dermie & greatly appreciate this! I run the code & attached is the screenshot of the result.

Is it possible to have it look more like screenshot 2 from original post?

Yes, if you highlight just the three rows and run the macro, it will copy and transpose what you have highlighted.
You decide how many to transpose, pretty much the same way you would if you manually did the process.

 

[sadams1]

I think we're on the same page with one 3 row chunk...this works great.

Ideally I'll like to be able to highlight 2 or more 3 row chunks & run the macro once.
The result I'd like is "screenshot 2" where the 2 sets of 3 row chunks would be highlighted then paste transposed.
I can't figure out how to code a macro to copy 1 3 row chunk, paste it, then skip to the next 3 row chunk & do the same until all the 3 row chunks are copied & pasted like in screenshot 2.

Again, I greatly appreciate your time!

 

[dermie_72]

See if this works any better for you. You have to select the text you want it to transpose....it's defaulting to column C, but you can change that with the J Reference.

Sub sadams1_Revised()
Dim A As Long, I As Long, J As Long
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.DisplayAlerts = False
A = Selection.Cells(1).Row
For I = 1 To Selection.Cells.count
    For J = 3 To 5
        Cells(A, J).Value = Selection.Cells(I).Value
        I = I + 1
    Next
    A = A + 3
    I = I - 1
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

 

[sadams1]

AMAZING! Exactly what will be of tremendous help. Thank you very much!