need a macro to copy current selection. Please help!

[pedie]

Hi,

I am not sure if this is posible?
In sheets("Data").range (I43:I53), if I click on any of the cell can I make the macro to just copy value of that cell
so that I dont have to ctrl+c and is ready to be pasted somewhere...

Thanks for helping.
Pedie

 

[Joe4]

It would be a real simple macro, only needs one line:

Selection.Copy

 

[Demosthenes&Locke]

Actually I think they're saying if you click within a certain group of cells, have it automatically copy just the value to the clipboard.


Maybe the solution would be a macro that placed a button over those cells displaying the value of the cell that when clicked would automatically copy that value into the clipboard.

 

[VoG]

Perhaps in the sheet's code module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("I43:I53")) Is Nothing Then Target.Copy
End Sub

 

[Joe4]

Reading your comments and going back and reading the original question, you are probably right. I didn't get exactly what they were asking the first time around.

 

[pedie]

Vog, you are amazing.
That is it. PERFECT!

Thanks alot!!!!

 

[Demosthenes&Locke]

Modified Vog's solution in case you have a formula and don't want to paste the formula:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim DataObj As New MSForms.DataObject
Dim S As String
If Not Intersect(Target, Range("I43:I53")) Is Nothing Then
     Target.Select
     tobepasted = ActiveCell.Text
     DataObj.SetText tobepasted
     DataObj.PutInClipboard
End If
End Sub

Of course it won't show that it has just copied that cell, but it does copy it to the clipboard.

 

[VoG]

For that to work you'll need to create a reference to Microsoft Forms 2.0 Object Library in Tools > References

 

[Demosthenes&Locke]

I've done so many things to my excel I really don't know what all is required for stuff I do to work on other machines.