Double click in cell and copy the value into another cell

[asyamonique]

Good Day and Happy New Year,
The datas has stored in columns "a:g" all the way down.
When i double click any cell in column "A" I would like to store the value one by one into the column "K" from first cell to down.Is it possible?

 

[Piplfox]

OK, try this in a copy of your workbook. To implement ..

1. Right click the Sheet1 name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim r As Long

If Target.Column = 1 Then
Cancel = True
With Sheets("Sheet2")
r = .Range("K" & Rows.Count).End(xlUp).Row + 1
r = IIf(r < 10, 10, r)
.Range("K" & r).Value = Target.Value
End With
End If
End Sub

Thanks , this is exactly what I need. Thank you againray:

 

[Piplfox]

OK, try this in a copy of your workbook. To implement ..

1. Right click the Sheet1 name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim r As Long

If Target.Column = 1 Then
Cancel = True
With Sheets("Sheet2")
r = .Range("K" & Rows.Count).End(xlUp).Row + 1
r = IIf(r < 10, 10, r)
.Range("K" & r).Value = Target.Value
End With
End If
End Sub

Is it possible to add some more code that double click copy A and B cell from sheet1 to K and L cell sheet2 insted only A cell sheet1 to to K cell sheet2.

 

[Peter_SSs]

Is it possible to add some more code that double click copy A and B cell from sheet1 to K and L cell sheet2 insted only A cell sheet1 to to K cell sheet2.

See if this is what you mean. It should allow you to double click any cell in column A or B of Sheet1 and have the pair of cells copied to Sheet2, from row 10 onwards. This code replaces the previous code.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> rK <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, rL <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> Target.Column < 3 <SPAN style="color:#00007F">Then</SPAN><br>        Cancel = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet2")<br>            rK = .Range("K" & Rows.Count).End(xlUp).Row + 1<br>            rL = .Range("L" & Rows.Count).End(xlUp).Row + 1<br>            rK = IIf(rK < 10, 10, rK)<br>            rK = IIf(rK < rL, rL, rK)<br>            .Range("K" & rK).Resize(, 2).Value = _<br>                Cells(Target.Row, 1).Resize(, 2).Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>

 

[MNsparky]

I have a different twist on my question. I'm using the code below to double click on a range of cells between (F14:F25) to put the value of the cell into (F27); works great.

Could you tell me how to modify this code to double click additional cells between (F63:F66) and put that value into cell (F68), in addition to the existing coding?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("F14:F25")) Is Nothing Then
Range("F27").Value = Target.Value
End If
End Sub

 

[Peter_SSs]

Here's one way that should do what I think you are asking.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> DestRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("F14:F25,F63:F66")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        DestRow = IIf(Target.Row > 25, 68, 27)<br>        Range("F" & DestRow).Value = Target.Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>

 

[MNsparky]

Thank you

 

[savageoz]

Thank you all for taking the time to help us with the issues we run across.

I am trying to make a draft board for my fantasy football league. With the examples here, along with some other research i have done, I have figured how to make this work for the draft board. I would also like to make the cell color change when it is double clicked.

For example: when I click on a players name in the sheet titled "Draft List", I would like it to change the color of the cell chosen, as well as copy the contents into the next available cell in the sheet titled "sheet 1". How would I go about incorporating these two together?

here is the code that I am using now

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim r As Long

If Not Intersect(Target, Range("2:100")) Is Nothing Then
Cancel = True

With Sheets("Sheet1")
r = .Range("c" & Rows.Count).End(xlUp).Row + 1
r = IIf(r < 1, 1, r)
.Range("c" & r).Value = Target.Value
End With


End If

End Sub

 

[savageoz]

Or is it possible to strike a line through the text instead of color change?

 

[Peter_SSs]

Thank you all for taking the time to help us with the issues we run across.

I am trying to make a draft board for my fantasy football league. With the examples here, along with some other research i have done, I have figured how to make this work for the draft board. I would also like to make the cell color change when it is double clicked.

For example: when I click on a players name in the sheet titled "Draft List", I would like it to change the color of the cell chosen, as well as copy the contents into the next available cell in the sheet titled "sheet 1". How would I go about incorporating these two together?

here is the code that I am using now

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim r As Long

If Not Intersect(Target, Range("2:100")) Is Nothing Then
Cancel = True

With Sheets("Sheet1")
r = .Range("c" & Rows.Count).End(xlUp).Row + 1
r = IIf(r < 1, 1, r)
.Range("c" & r).Value = Target.Value
End With

Target.Font.Strikethrough = True
End If

End Sub

Welcome to the MrExcel board!

I think you are pretty much there. I don't think you need the line I have colored red so perhaps you could remove that.
The blue line I have added to your code should give you the strikethrough.

 

[savageoz]

It works perfect.

I had never heard of vba until 3 days ago when I started researching a way to build a draft board. When I tried to incorporate the color codes along with the code, I couldn't get it to work because I couldn't figure out where in the code it would go.

I use Excel quite a bit at work, and will be visiting this forum regularly in the future.

Thank you very much.