Forum Discussion
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 user can enter information into a data table, and I then pull all information into another sheet creating a false table which brings in new organizations they added, and new work class they added (If they add them, this does not happen all the time.) and then the body of the table is a countifs option that counts the matching criteria per organization/class of work. I need to transpose paste the class work data as the "Header" columns. All help appreciated!
Private Sub testTranspose()
ThisWorkbook.Worksheets("Admin").Activate
ThisWorkbook.Worksheets("Admin").ListObjects("OrgD").Range.Select
Selection.Copy
ThisWorkbook.Worksheets("Sheet1").Activate
ThisWorkbook.Worksheets("Sheet1").Range("C26").PasteSpecial Transpose:=True
End Sub
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
4 Replies
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 SubDid you want it to work like this? :-
- Not for this instance, but I do like this and may use it for future projects. Thank you!
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- This worked great! Thank you, I really appreciate your help!
