3

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

Stefan Steiger
  • 78,642
  • 66
  • 377
  • 442
FH Yousif
  • 33
  • 1
  • 1
  • 3
  • 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](http://stackoverflow.com/questions/27169070/identifying-a-worksheet-other-than-by-its-name/27169154#27169154) – Ioannis Dec 29 '14 at 21:07
  • 1
    @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 Dec 29 '14 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 Dec 29 '14 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?](http://superuser.com/questions/366468/what-is-the-maximum-allowed-rows-in-a-microsoft-excel-xls-or-xlsx) – Chrismas007 Dec 29 '14 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 Dec 29 '14 at 22:25

1 Answers1

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
John Smith
  • 7,243
  • 6
  • 49
  • 61
  • 1
    You should add that `PasteSpecial` is unavailable with `Cut` – Chrismas007 Dec 29 '14 at 21:06
  • 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 – FH Yousif Dec 29 '14 at 21:17