VBA Copy & Transpose Data Range

Question

I am working on setuping a few spreadsheets at work to streamline my work. I'm still new to VBA.

I am trying to cut a range of data in a column (E6:E14) from Sheet1 and transposing the data before pasting the data in the next available row in Column A of Sheet2. Here is the code that I have written so far from trial and error. Everytime I run the code I get a Run-time error '1004'. I am trying to create a "database" in Sheet2. Any help is appreciate it.

Sub Test()
    Worksheets("Sheet1").Range("E6:E14").Cut
    Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
End Sub

Thanks for the help!

FHY

Answer 1

PasteSpecial is unavailable with the .Cut method, but not the .Copy method. When I changed

Worksheets("Sheet1").Range("E6:E14").Cut

to

Worksheets("Sheet1").Range("E6:E14").Copy

everything worked fine. If you want everything deleted afterwards, you could always just do:

Sub Test()

Worksheets("Sheet1").Range("E6:E14").Copy

Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True

Worksheets("Sheet1").Range("E6:E14").Clear 

End Sub