Copy a cell on double click and paste to a different cell

[admack]

Hi,

I am looking for a bit of steer on some VBA that will allow me to:

- Double click on a cell within a range (C11:AI24)
- On Double clicking, it will take the content of the cell I have double clicked on (could be any cell in the above range) and pastespecial values into a different cell (C28)

It is worth also noting that the cells that I would like to double click on have VLOOKUP formula in them.

Any help would be greatly appreciated. I have tried a couple of things, however it just enters me into the formula, and doesn't actually do anything.

Thanks
Adam

 

[oldbrewer]

single click to highlight the cell, then single click a macro button seems the obvious way the macro trigger could be a over A1 with top row and left column locked so even if you select AI124 the macro button in on screen

 

[mart37]

Put this code between the sheet:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)Cancel = True
If Intersect(Range("C11:AI24"), Target) Is Nothing Then Exit Sub
[C28] = Target.Value
End Sub

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("C11:AI24")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Range("C28").Value = Target.Value
End If
End Sub

 

[admack]

Put this code between the sheet:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)Cancel = True
If Intersect(Range("C11:AI24"), Target) Is Nothing Then Exit Sub
[C28] = Target.Value
End Sub

Lesson learned - I had tried this code before and it didn't work.... probably best to assign it to the relevant worksheet.

Thanks all - problem solved!

 

[mat3usPT]

Hey guys, this is great. Thank you so much.
Now i have a question. Is it possible to double click a cell and copy it it to a different sheet on a specific cell using vlookup?

For example, I want to double click on cell J2 here and send the value to the sheet "Slots", on the third collumn of the row that has the same game name

Do you know if this is possible?
I tried the following code but it didn't work
Thank you for the time

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("J2:J72")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Range("VLOOKUP(Q2;Slots!$A$2:$Z$986;2;FALSE)").Value = Target.Value
End If
End Sub