pastespecial error 400

John Musbach said:

Hi, I'm trying to copy a user selected area of a spreadsheet and then paste just the values of the selection (so that the formulas aren't copied over as they would no longer be valid) using pastespecial but currently all that is returned is a cryptic error "400". What am I doing wrong? The code is below. Thanks!

Code:

Selection.Copy
Sheets.Add.Name = "Pasted"
Sheets("Pasted").PasteSpecial Paste:=xlPasteValues

Click to expand...

snowblizz said:

Been running into those myself. It has something to do with ranges it seems.
Try pasting to "Worksheets("Pasted")", I did something similar just now for another case and it worked where earlier just sheet or plain range didn't.

Click to expand...

jonmo1 said:

Welcome to the board...

Many events in macro code will clear the contents of the clipboard..

So when you copy the range, then add a sheet (I think that's when it clears the clipboard).
Now there is nothing in the clipboard, so nothing to paste = error
Also, you didn't tell it where to paste (which cell)

Try this

Code:

Set Myrange = Selection
Sheets.Add.Name = "Pasted"
MyRange.Copy Destination:=Sheets("Pasted").Range("A1")

Click to expand...

jonmo1 said:

Welcome to the board...

Many events in macro code will clear the contents of the clipboard..

So when you copy the range, then add a sheet (I think that's when it clears the clipboard).
Now there is nothing in the clipboard, so nothing to paste = error
Also, you didn't tell it where to paste (which cell)

Try this

Code:

Set Myrange = Selection
Sheets.Add.Name = "Pasted"
MyRange.Copy Destination:=Sheets("Pasted").Range("A1")

Click to expand...

jonmo1 said:

my previous suggestion would paste the whole thing, not just values.

Try

Set Myrange = Selection
Sheets.Add.Name = "Pasted"
MyRange.Copy
Sheets("Pasted").Range("A1").PasteSpecial xlpastevalues

Click to expand...