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

Note that Rows.Count refers to whichever sheet is active. I would qualify it, and use the sheet's code name instead of its actual name — Ioannis
– Ioannis, Commented Dec 29, 2014 at 21:07
@loannis Every sheet's Rows.Count will be the same (the maximum allowed by Excel) unless you changed the sheet to reflect a different maximum (which is very rare). — Chrismas007
– Chrismas007, Commented Dec 29, 2014 at 22:09
@Chrismas007 in that case, you could use a constant instead of calculating the rows.count every time. I believe the max rows is 1048576 — John Smith
– John Smith, Commented Dec 29, 2014 at 22:17
@user3739391 You could do that, but when transporting code across multiple versions of Excel, the maximum could change: What is the maximum allowed in Excel? — Chrismas007
– Chrismas007, Commented Dec 29, 2014 at 22:21
@Chrismas007 I don't know anyone using 2003 or earlier.. not saying they couldn't raise the rowcount in versions to come though (that would be nice). there are lots of things that could change from version to version though, apart from just row #'s.. but you are right, instead of hardcoding the constant, just set it equal to rows.count so you only calc it once. — John Smith
– John Smith, Commented Dec 29, 2014 at 22:25

John Smith · Accepted Answer · 2014-12-29 21:08:10Z

3

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

edited Dec 29, 2014 at 21:08

answered Dec 29, 2014 at 20:56

John Smith

7,4477 gold badges52 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chrismas007 Over a year ago

You should add that PasteSpecial is unavailable with Cut

FH Yousif Over a year ago

That worked brilliantely. I wanted to use Cut to clear the data from the entry field. I was not aware that Cut was not available with special paste.Thank you for your help1

Collectives™ on Stack Overflow

VBA Copy & Transpose Data Range

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related