Copying a VBA variable into the Clipboard

[Warminster]

Maybe this is an easy question, but I don't know how to do this.

I have a macro that copies a cell, and then uses VBA to modify that information. I'd like to be able to put that information into the clipboard, so that I can paste the information into any cell that I choose.

How do I copy my variable into the clipboard.

 

[Juan Pablo González]

Well... I know Chip has some code to put and read stuff from the Clipboard (www.cpearson.com) BUT... if it's a variable, you could just do this:

MyVar = Range("A1").Value
MyVar = MyVar * 5 + 50
Range("A2") = MyVar

Or am I way off of what you need ?

 

[Warminster]

i don't want to have to specify the range. I want to be able to use this to copy and then paste to anywhere.

 

[Juan Pablo González]

Ah.. I see, something like run a macro to put the variable in the clipboard, and you can use Control V to store it in the cell ?

Mhmm... let me think about it.

 

[NateO]

Wow, kudos on the manners sir, nice to see a helpful guy like Juan barked at, you weren't raised in a barn by chance?

Here's two ways of doing this.

1.<pre>
Dim n 'first line in module

Sub cpy()
n = ActiveCell.Value
End Sub

Sub pste()
ActiveCell.Value = n
End Sub</pre>

2.<pre>
Sub otherway()
Selection.Copy
End Sub</pre>

Good luck.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-06-21 15:08

 

[Juan Pablo González]

Thanks Nate, and nice simple approach !

 

[Warminster]

On 2002-06-21 14:51, Juan Pablo G. wrote:
Ah.. I see, something like run a macro to put the variable in the clipboard, and you can use Control V to store it in the cell ?

Mhmm... let me think about it.

Thats exactly what I am looking for. I don't want to have to select the destination cell in my code. I want to have the ability to paste the cell to another cell at a later time. I don't know if that cell will be in the same sheet, same workbook, etc. I want to be able to copy the cell, have my code perform an action on it, and store it on the clipboard. If I have to have my code post the result, it defeats the purpose I am trying to achieve.

Thanks

 

[Warminster]

On 2002-06-21 15:04, NateO wrote:
Wow, kudos on the manners sir, nice to see a helpful guy like Juan barked at, you weren't raised in a barn by chance?

I'm sorry, what? Can you please quote the line where I "barked" at Juan? I certainly did not intend to be rude, and apologize if I came off that way. After rereading the thread several times, I can only find one statement that is offensive or objectional in any way. Yours.

Here's two ways of doing this.
1.
<pre>
Dim n 'first line in module

Sub cpy()
n = ActiveCell.Value
End Sub

Sub pste()
ActiveCell.Value = n
End Sub</pre>

2.
<pre>
Sub otherway()
Selection.Copy
End Sub</pre>

Neither of your two suggestions quite does what I want. Option 1 requires my code to paste the value. This limits my flexibility as I am no longer able to paste anywhere.

Option 2 does put the value into the clipboard, but does not allow me to perform any modifications to the value before it is put there.

I apologize that my original post was unclear on exactly what I was trying to do. I appreciate all your help in trying to figure this out.

Thanks

 

[NateO]

On 2002-06-21 14:46, Warminster wrote:
i don't want to have to specify the range. I want to be able to use this to copy and then paste to anywhere.

I guess this didn't strike me as curteous.

It depends what type of data your copying is, if it's numeric, dim your variable as such, you don't need to paste the variable:<pre>
Dim n As Long

Sub cpy()
n = ActiveCell.Value
End Sub

Sub mssWn()
MsgBox (n / 2)
End Sub<pre>

You can paste it later with the code above, it's stored, not sure why you ran the pst() procedure right away....Just an example. Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-06-23 15:05

 

[Juan Pablo González]

Did you check Chip's webpage ? he has some code to handle the clipboard.