Snasui Excel Forum

Code: Select all

Sub Paste_ValuesOnly_Transpose()
Selection.PasteSpecial Paste:=xlValues, Transpose:=True
Application.CutCopyMode = False
End Sub

Code: Select all

Dim i As Integer, j As Integer
Dim rngAll As Range, tgRng As Range

Set rngAll = Selection
Set tgRng = Range("e20")
j = Selection.Rows.Count

For i = 1 To rngAll.Areas.Count
'    Selection.PasteSpecial Paste:=xlValues, Transpose:=True
'    Application.CutCopyMode = False
    rngAll.Areas(i).Copy
    If i = 1 Then
        tgRng.PasteSpecial Paste:=xlPasteValues, Transpose:=True
    Else
        tgRng.Offset(0, j * (i - 1) + 2).PasteSpecial Paste:=xlPasteValues, _
            Transpose:=True
    End If
    Application.CutCopyMode = False
Next i