VB Excel PasteSpecial requiring clipboard content?

Question

I'm having a problem with VB PasteSpecial.
This code works perfectly in Excel VB (given that you have selected cells with data)

Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
Application.CutCopyMode = False

However, I'm using a third-party software (QlikView) that I extract the data from, which is then supposed to be copied into the Excel document. There is no problem with the normal paste but it MUST be transposed.

Obviously, since I dont have any content in the workbook to copy, I don't use

Selection.Copy

But because I don't copy anything from the document first (even though there are table data in the copy memory), this call returns bad argument exception (this also happens if I copy cells in that VERY workbook first and then just call the macro for transposing it).

Runtime error '1004' returned. PasteSpecial method of Range class failed.

Yes, I can paste it into the document, then cut it from the area, move it to the correct place and transpose it, but that is bad coding.

Have any of you experienced this and got a way to get this working ?

Answer 1

You will have to use the method as you mentioned above. You can also try this

Range("A1").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
Application.CutCopyMode = False

But you will have to copy it again and transpose it. Other wise there is no direct way you can transpose it.

Answer 2

The reason that you cannot use PasteSpecial to transpose the data is due to the format of the data as it resides in the clipboard after you copy it from QlikView.

When you copy data from a QlikView table (which I assume you are copying from), it copies it to the clipboard in three formats: HTML, Unicode and standard (code-paged) text:

Comparing this with Excel's clipboard formats:

As you can see, when copying data in Excel, it stores the data in the clipboard in its own format and as such knows how to transpose the cells if required. For QlikView, the clipboard just contains plain text, therefore Excel does not know how to transpose this and as a result the PasteSpecial call fails.

If you are copying from a table in QlikView to Excel, I would recommend performing the transposition already in QlikView if you can by using a "Pivot Table" chart in QlikView (as you can drag the columns and rows around how you wish). Otherwise you will have to use Siddharth's code and transpose it once it's in Excel.