CODEWITHSUNDEEP
vbaexcel
Collin Stacey
Collin Stacey

Reputation: 31

VBA: PasteSpecial method of Range class failed

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

Upvotes: 2

Views: 569

Answers (1)

Scott Craner
Scott Craner

Reputation: 152605

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

Upvotes: 3

Related Questions