Macro to post values

[Arcturus16a]

I would like to create a macro that allows me to paste values of something copied from one range to another.

The macro should paste from the clipboard any copied range from any source (another sheet or workbook) and should paste those values into (starting at) a user-selected range.

I thought this would work, but Error msg 1004 indicates that the Paste Special method of Range has failed. What can I do to make this work?

Sub PasteValue()
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

 

[Micron]

Maybe one of the answers here will help

I want to copy multiple cells as one sentence to paste in another program

Hey, I'm looking to create a table with pre-determined sentences in, so that all I need to do is add a person's name in the middle cell, and hit copy on a command button, and I will have copied the multiple cells, to be able to copy as one sentence in another program elsewhere. I have found a...

www.mrexcel.com

 

[Arcturus16a]

Maybe one of the answers here will help

I want to copy multiple cells as one sentence to paste in another program

Hey, I'm looking to create a table with pre-determined sentences in, so that all I need to do is add a person's name in the middle cell, and hit copy on a command button, and I will have copied the multiple cells, to be able to copy as one sentence in another program elsewhere. I have found a...

www.mrexcel.com

Well certainly the one answer you refer to doesn't approach pasting values. Concatenation is not the same.
Do you have references to other answers?

 

[Micron]

The macro should paste from the clipboard

Well, it shows how to copy to the clipboard and then paste from the clipboard. The concatenation part is of no consequence?
Why would you not just copy from one range and paste to another without using the clipboard? Something like

Range("A1:F1").Copy Sheets("Sheet8").Range("A1:F1")

or can use variables if the range is dynamic.

 

[Arcturus16a]

Concatenate is not in my initial post, so let's please get back to my initial question.

1) I want to copy any cell or range of cells (not part of the macro);
2) Then move to some/any target cell in another range (whether same sheet or a different sheet or workbook) (also not part of the macro);
3) Then paste values using the macro that I'm asking for help

 

[Micron]

Concatenate is not in my initial post, so let's please get back to my initial question.

I already said I know that and indicated it doesn't matter - the code contains a copy paste method. Moreover, you said you wanted to copy and paste to/from the clipboard - the code does that as well. Since you're fixated on the concatenation and didn't comment on the additional method I suggested in post 4, I think I cannot help you any further.

 

[tonyyy]

Arcturus16a,

Your Selection.PasteSpecial code worked on my sheets/workbooks.

One reason for the 1004 error would be if there's nothing on the Clipboard. So before running the code, note the Paste icon in the upper left of Excel... Is it grayed out? If so then the code will throw the error.

And why would there be nothing on the Clipboard? Perhaps the workbook/sheet you're going to has a Worksheet_Change or Worksheet_SelectionChange routine (or some other routine) that empties the clipboard before you have a chance to run your PasteSpecial code.

Cheers,

Tony

 

[offthelip]

I use exactly the code you posted all the time, I assign it to a shortcut key and keep it in my personal.xlb file so it is always available