pastespecial error 400

[John Musbach]

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!

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

 

[snowblizz]

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!

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

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.

 

[Jonmo1]

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

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

 

[John Musbach]

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.

Thanks but no change. One oddity that I did notice is that running the macro through excel returns error "400" while running the macro through the VBA editor returns error "1004" if that helps any. I stepped through the code using the debug feature and indeed it is this pastespecial line that it's unhappy about, but why? From what I've read online this should work, but then again I guess I haven't seen a lot of people trying to paste across workbooks...

 

[snowblizz]

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

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

So THAT is the problem. I've been looking at several copy/paste errors this week and couldn't find a good explanation. What you say makes sense, I've noticed when doing manual work that it "forgets" what was selected in many cases.

Note that my code above has an error as the paste requires a range to paste to.

 

[Jonmo1]

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

 

[John Musbach]

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

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

Thanks, that worked.

 

[John Musbach]

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

Ah, perfect. :D