2

I am using this to move orders from the new orders page to the previous orders page however I get the title error when I attempt to run it. I have looked at several different places to try to get it to work and I see that theirs should work but mine isn't.

Sheets("New_Orders").Range("B3:E29").Cut

Sheets("Previous_Orders").Range("B31").PasteSpecial Paste:=xlPasteValues

This is more code that is supposed to do the same thing that isn't working either

Sheets("New_Orders").Select
Range("B3:E29").Select
Selection.Cut
Sheets("Previous_Orders").Select
Range("B:B").Find("").Select
ActiveSheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

I have tried Selection.Copy as well. It gave the same error

Community
  • 1
  • 1
Collin Stacey
  • 31
  • 2
  • 3
    You can only paste all or insert with cut. – Scott Craner Dec 07 '16 at 21:14
  • 3
    [Must. Read.](http://stackoverflow.com/q/10714251/1188513) – Mathieu Guindon Dec 07 '16 at 21:16
  • Must watch: [Excel VBA Introduction](https://www.youtube.com/playlist?list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5). This is the relevant video: [Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)](https://www.youtube.com/watch?v=c8reU-H1PKQ&list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5&index=5&t=3072s) –  Dec 07 '16 at 22:07

1 Answers1

3

As stated above in the comment you can only Paste everything when using Cut. If only values are wanted then assign the values directly then clear the range.

Sub foo()
Dim rng As Range
Dim lastRow As Long

Set rng = Sheets("New_Orders").Range("B3:E29")
With Sheets("Previous_Orders")
    lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
    .Cells(lastRow, 2).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
End With
rng.Clear
End Sub
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 1
    But that doesn't explain the comment edited into the question, i.e. `I have tried Selection.Copy as well. It gave the same error` (But I can't get `ActiveSheet.PasteSpecial` to work anyway - I would have thought that it would need to be `ActiveCell.PasteSpecial` - so I'm not too sure how accurate the "same error" comment is.) – YowE3K Dec 07 '16 at 21:30
  • Was just typing the same when I saw your edit @YowE3K It should work with ActiveCell and Copy. – Scott Craner Dec 07 '16 at 21:36
  • When I run it it gives me Run-Time error '1004': Application-defined or object-defined error. – Collin Stacey Dec 07 '16 at 21:57
  • @CollinStacey on which line? – Scott Craner Dec 08 '16 at 00:53
  • @CollinStacey never mind I found the error see the edit. – Scott Craner Dec 08 '16 at 00:54