Copy and Paste a Column into a Row in excel using vba

Question

I am trying to write a macro that copies a range of cells (AA4:AA15)(e.g. AA4, AA5,AA6...AA15) and pastes these values into a new range (C3:N3)(e.g. C3, D3, E3,...N3). The values are found using a formula. I tried using the code seen below, but it only pasted the first value in my copy range, not all of the values. Any help is appreciated.

Range("C3:N3").Value = Range("AA4:AA15").Value

Solution

If you did this manually, you would use Paste Special->Transpose. So try:

Sub Macro1()
    Range("AA4:AA15"). Select
    Selection.Copy
    Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
End Sub

(Note that I'm only selecting the first cell C3, not the entire range C3:N3)

Excel has a great macro recorder that can help you learn VBA. Simply turn it on and do some stuff and the recorder will create a VBA macro with those exact actions.

Comments

[Mike]

I have already used 'record macro' to get a working code, but this can be a bit timely and not aesthetically pleasing(request from boss), so i am looking for a better solution.

[Mike]

I used the macro recorder and it worked, but took some time and isn't aesthetically pleasing(matters to my boss) so I came on here to see if there was a better way than recording just recording the macro

[PowerUser]

Yes, most computer generated code is ugly, but it is very helpful for learning. i.e. use the macro recorder to make the macro, see what methods it used, then edit it as you need.