Excel VBA Copy/Paste Transpose

Question

I need to copy cells from SheetA D3:D40 and paste transpose to SheetB C2:AN2.

This is the code I have:

Sub CopyPaste()
    Worksheets("Questions").Range("D3:D40").Select
    Selection.Copy
    Worksheets("Apps").Range("C2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True
    Worksheets("Apps").Range("A1").Select
    Application.CutCopyMode = False
End Sub

After I execute this I get Run Time Error 1004 Select method of Range class failed

What am I doing wrong?

You cannot `.Select` a range on an inactive sheet. `.Select` is not necessary. `Worksheets("Questions").Range("D3:D40").Copy` and `Worksheets("Apps").Range("C2").PasteSpecial Paste:=xlPasteValues, Transpose:=True` is all that is needed. — Scott Craner, Dec 06 '21 at 17:10
This is what happens when `Questions` is not active. You should avoid the use of `.Select` — Siddharth Rout, Dec 06 '21 at 17:10
You may want to see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Siddharth Rout, Dec 06 '21 at 17:10

score 0 · Answer 1 · answered Dec 06 '21 at 22:23

Copy this code:

Sub CopyPaste()
    With Worksheets("Questions")
        .Range("D3:D40").Copy
    End With
    With Worksheets("Apps")
        .Range("C2").PasteSpecial Paste:=xlPasteValues, Transpose:=True
    End With
    Application.CutCopyMode = False
End Sub

score 0 · Answer 2 · answered Dec 07 '21 at 03:28

Using Variables in a Copy/Paste Values Transpose

Option Explicit

Sub CopyPasteTranspose()
    
    ' Source
    Dim sws As Worksheet: Set sws = ThisWorkbook.Worksheets("Questions")
    Dim srg As Range: Set srg = sws.Range("D3:D40")
    
    ' Destination
    Dim dws As Worksheet: Set dws = ThisWorkbook.Worksheets("Apps")
    Dim dfCell As Range: Set dfCell = dws.Range("C2")
    Dim drg As Range: Set drg = dfCell.Resize(srg.Columns.Count, srg.Rows.Count)
    
    ' Copy by Assignment (only values (most efficient))
    drg.Value = Application.Transpose(srg.Value)
    
    ' Note that the 'Selection' didn't change. It is as if nothing had happened,
    ' yet the values were written. You possibly don't need the following lines.
    
    'dws.Activate
    'dws.Range("A1").Select
    
End Sub

Excel VBA Copy/Paste Transpose

2 Answers2

Using Variables in a Copy/Paste Values Transpose