PasteSpecial transpose:=True not working.

The code below keeps throwing an error: "Run-time error '1004': pasteSpecial method of Range class failed." I am not sure why it is throwing this error. I am setting up an application so that a use...

developer

excel

HansVogelaar

Apr 15, 2022

ADGToreUp

By default, PasteSpecial performs a full paste. But you cannot transpose a table complete with its structure. You can paste the values and formatting:

Private Sub testTranspose()
    ThisWorkbook.Worksheets("Admin").ListObjects("OrgD").Range.Copy
    ThisWorkbook.Worksheets("Sheet1").Range("C26").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Transpose:=True
End Sub

amit_bhola

Iron Contributor

Apr 15, 2022

ADGToreUp , Going a step further from Mr. HansVogelaar solution,

Private Sub testTranspose2()

    ThisWorkbook.Worksheets("Admin").ListObjects("OrgD").Range.Copy
    ThisWorkbook.Worksheets("Sheet1").Range("C26").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Transpose:=True
    ThisWorkbook.Worksheets("Sheet1").Range("C26").PasteSpecial Paste:=xlPasteFormats, Transpose:=True

End Sub

Did you want it to work like this? :-

ADGToreUp
Brass Contributor
Apr 15, 2022
Not for this instance, but I do like this and may use it for future projects. Thank you!

Forum Discussion

PasteSpecial transpose:=True not working.

Resources