Search code examples
vbarangecopy-pastetranspose

Copy and Paste Special for tranposing data VBA

I'm sure this must be a silly problem. I'm stuck in copying a range of data from one sheet into another worksheet. It works perfectly fine with regular data. However, when I try to use PasteSpecial for transposing the data. It doesn't work. I don't get any errors or pop-ups.At the same time, I see data has been not copied.

my successful code is 

With ActiveWorkbook.Sheets("Consolidate_Data")
    .Range(.Cells(1 + j, 1), .Cells(350 + j, 3)).Copy _
      Destination:=ActiveWorkbook.Sheets("Template").Cells(77, 1)
   End With

The one I have problem with is

With ActiveWorkbook.Sheets("Consolidate_Data")      
.Range(.Cells(1+ j, 4), .Cells(350 + j, 51)).Copy _ 
.Sheets("Template").Cells(427, 1).PasteSpecial Paste:=xlPasteValues,Operation:=xlNone, SkipBlanks:=False, Transpose:=True 
End With

Any ideas and suggestion would be really helpful. Thank you in advance

Solution

  • Fully reference where you are pasting. No need for line continuation.

    With ActiveWorkbook.Sheets("Consolidate_Data")
    .Range(.Cells(1 + j, 4), .Cells(350 + j, 51)).Copy
    ActiveWorkbook.Sheets("Template").Cells(427, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End With