Copy Values (Not looking for Paste Values)

[PKFillmore]

Hi, im looking to create a something with VBA that will allow users to copy (values only) to the clipboard.
I know all about Paste Values however Paste Values does not work in other programs we use here at work. (Lotus notes, Java forms, etc)

I can easily come up with the code to "copy" the values of the active cell however im having difficulty on doing it with a range larger than 1

Here is my code so far....

Dim MyVar as String
Dim clipboard As MSForms.DataObject
Set clipboard = New MSForms.DataObject

MyVar = Activecell.value

clipboard.SetText MyVar
clipboard.PutInClipboard

I was thinking about using a for each cell in selection loop.. but im not sure how to form it so that if the user tries to paste it back in excel it would resume the same cell order...

For Each cell In Selection
    MyVar = Cell.Value

'code that stores MyVar in a range
'and will paste in the same order it was copied as

Next

Any ideas would greatly be appreciated!

 

[Andrew Poulsom]

In what way doesn't Excel's Copy method work when you try to Paste into another application?

 

[PKFillmore]

In what way doesn't Excel's Copy method work when you try to Paste into another application?

My problem is not with the copy method but with the < paste values >
For example, We have a list of ID numbers in excel that if were were to copy/paste it into a lotus notes email it will asume the form of a table (im looking to just paste the text thats in it)

I have a few other examples I can think of as well..

 

[Andrew Poulsom]

Don't you have the option to paste only text?

 

[PKFillmore]

Don't you have the option to paste only text?

Yes you are correct we can paste text only. The reason I was looking into doing this is because I wanted to be able to add an extra feature in the copy method.

Depending on the report we are working with we sometimes have to drop the first or last digits of the ID number.
My end goal was to be able to modify the string from the report directly with the copy method before putting it in the clipboard

MyVar = Mid(Activecell.value, X, Y)

The arguments for X and Y would be passed down from an option I would create in the ribbon

[/URL][/IMG]

 

[Andrew Poulsom]

In that case you could use a formula in a spare column and copy the results.