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

HansVogelaar

MVP

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

ADGToreUp

Brass Contributor

Apr 15, 2022

This worked great! Thank you, I really appreciate your help!

Forum Discussion

PasteSpecial transpose:=True not working.

Resources