0

I'm trying to copy row values to other rows but I need them transposed. I've the following code but it doesn't work.

Sub Prueba()
    Worksheets("Punto 5").Range("J9:M9").Copy
    Worksheets("Punto 5").Range(Cells(ActiveCell.Row + 1, ActiveCell.Column)).PasteSpecial Transpose:=True
End Sub

I checked this answer but it didn't help me at all. Excel VBA - Range.Copy transpose paste

Any help please?

Community
  • 1
  • 1
Jrgsua83
  • 3
  • 4

2 Answers2

1

just remove the Range( from the second function call

It should be:

Sub Prueba()
    Worksheets("Punto 5").Range("J9:M9").Copy
    ActiveCell.offset(1, 0).PasteSpecial Transpose:=True
End Sub
cyberponk
  • 1,585
  • 18
  • 19
  • 1
    While that is a correct answer, I believe the OP never intended `Worksheets("Punto 5")` to *not* be active when that executes, and the chances are they wanted `ActiveCell.Offset(1,0).PasteSpecial`. – GSerg Nov 03 '16 at 17:19
  • indeed this is much better! – cyberponk Nov 03 '16 at 17:26
0

I'm not by any PC to test it, but here's an alternative without Copy/Paste;

Sub Prueba()
    With Worksheets("Punto 5").Range("J9:M9")
        ActiveCell.Offset(1).Resize(.Rows.Count).Value = Application.Transpose(.Value)
    End With
End Sub
user3598756
  • 28,893
  • 4
  • 18
  • 28