ActiveSheet.PasteSpecial works, error with Transpose:=True

Question

I'm doing a macro that copies the selection in the currently active worksheet and pastes it in another, with transpose. I've seen questions relating to similar issues, but all seem to be much more complex, whereas this question seems simple (and baffling) to me.

The following code works (without transpose):

Sub sbCopyRangeToAnotherSheet2()
  Selection.Copy
  Sheets("snippets").Activate
  ActiveSheet.PasteSpecial 
End Sub

The following code throws a

1004 error - application-defined or object-defined error

(only change: added transpose):

Sub sbCopyRangeToAnotherSheet2()
  Selection.Copy
  Sheets("snippets").Activate
  ActiveSheet.PasteSpecial transpose:=true
End Sub

You are telling excel that you want the paste to be transposed, but you are not telling what do you want to paste. `ActiveSheet.PasteSpecial xlPasteValues, transpose:=true` for example. Also, you should look on how to avoid using `.Activate` `.ActiveSheet` `.Select` — Damian, May 03 '19 at 10:19
@Damian *"what do you want to paste"* of course it pastes *what* you copied before with `.Copy`? Actually the first code works so there is no general issue with the code. — Pᴇʜ, May 03 '19 at 10:20
@Pᴇʜ don't you need to tell excel what do you want to paste? `xlPasteValues` or `xlPasteFormats` or `xlPasteAll`... — Damian, May 03 '19 at 10:22
@Damian According to the documentation of the [Range.PasteSpecial method](https://learn.microsoft.com/en-us/office/vba/api/excel.range.pastespecial) all parameters are `Optional` that means you don't need to specify any of them. Eg the `Paste` parameter defaults to `xlPasteAll` if not specified. — Pᴇʜ, May 03 '19 at 11:08
Adding xlPasteValues doesn't change anything, as in ActiveSheet.PasteSpecial xlPasteValues, Transpose:=True — Francis, May 03 '19 at 11:22
@Pᴇʜ I tried adding Debug.Print Selection.Address just before the pastespecial command, the error message remains the same — Francis, May 03 '19 at 11:24

score 3 · Accepted Answer · answered May 03 '19 at 11:07

3

It does not work because the Worksheet.PasteSpecial method has no Transpose parameter. Note that there are 2 different PasteSpecial methods:

Range.PasteSpecial method

PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

Worksheet.PasteSpecial method

PasteSpecial(Format, Link, DisplayAsIcon, IconFileName, IconIndex, IconLabel, NoHTMLFormatting)

And you used the second one (which has no Transpose parameter and therefore errors).

The correct syntax would be:

Option Explicit

Public Sub sbCopyRangeToAnotherSheet2()
    Selection.Copy
    ThisWorkbook.Sheets("snippets").Range("A1").PasteSpecial Transpose:=True
End Sub

You might benefit from reading How to avoid using Select in Excel VBA.

answered May 03 '19 at 11:07

Pᴇʜ

56,719
10
49
73

1

Well spotted sir. – SJR May 03 '19 at 11:25
Thanks @Pᴇʜ This clears up some of the confusion I've been having with Excel vba. You're on the doc of a command and you get errors, despite using the indicated syntax. Or: you can remember a parameter on a command but you no longer see it in the doc. And the error messages don't give much clues! – Francis May 03 '19 at 12:27
@Francis Therefore always have a look at the explanation what `expression` means in the doc and for which object the documentation actually is. Every doc has a sentence like *"expression A variable that represents a Range object."* – Pᴇʜ May 03 '19 at 12:32
@Pᴇʜ how do I paste into the active cell of the target sheet? I've tried ThisWorkbook.Sheets.Item("snippets").Range(ActiveCell), ThisWorkbook.Sheets("snippets").Range(ActiveCell), ThisWorkbook.Sheets("snippets").ActiveCell... – Francis May 03 '19 at 12:37
1

@Francis `Application.ActiveCell.PasteSpecial Transpose:=True` but better to specify the cell by its address because there is only one active cell per Excel not per sheet. – Pᴇʜ May 03 '19 at 12:38

ActiveSheet.PasteSpecial works, error with Transpose:=True

1 Answers1