VBA Range.Copy transpose paste 1004 Error

Question

I wrote this code, but it only works when sheet1 is active. If sheet2 is active and I run the code, there is 1004 error. I don't know why. Thank you in advance!

Public Sub trans()

    Worksheets("Sheet1").Range(Cells(1, 1), Cells(3, 2)).Copy
    Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True

End Sub

Hi K.Davis! Thank you!!!! I thought worksheet.("sheet1") is used to active sheet1. — qiao, Nov 10 '18 at 05:00

CIAndrews · Answer 1 · 2018-11-10T04:35:27.750

0

Before you can select a range from a different worksheet, that worksheet needs to be active. E.g. something like this:

Public Sub trans()
  Range(Worksheets("Sheet1").Cells(1, 1), Worksheets("Sheet1").Cells(3, 2)).Copy
  Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True
End Sub

edited Nov 10 '18 at 04:35

answered Nov 10 '18 at 04:32

CIAndrews

1,046
10
19

_"Not entirely sure why..."_ would be because you are referencing the `ActiveSheet` when you don't explicitly qualify your ranges with the target sheet. – K.Dᴀᴠɪs Nov 10 '18 at 04:35
Yes, correct. Added that – CIAndrews Nov 10 '18 at 04:35
Hi CIAndrew! Thank you very much for the corrected code! It works now!! I thought Worksheet("Sheet1") is the way to activate sheet1, so why don't I need to qualify in the second row? Thank you : ) – qiao Nov 10 '18 at 05:21
Pasting doesn't require activation, selection is enough: See also [Selecting vs activate](https://stackoverflow.com/a/7185748/5785085) and [VBA paste range](https://stackoverflow.com/a/18969066/5785085) – CIAndrews Nov 10 '18 at 05:39

score 0 · Accepted Answer · answered Nov 10 '18 at 06:43

Use the Range(“address”) notation, to make Worksheets("Sheet1") reference suffice:

Public Sub trans()

    Worksheets("Sheet1").Range("A1:B2").Copy
    Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True

End Sub

Or reference Worksheets("Sheet1") in a With...End With block to have Cells() reference it:

Public Sub trans()

    With Worksheets("Sheet1")
        .Range(.Cells(1,1), .Cells(3,2)).Copy
    End With  
Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True

End Sub

VBA Range.Copy transpose paste 1004 Error

2 Answers2