Double click macro, copy contents from one cell to another (different rows)

[CAMARD2]

Hey all,

I needsome help and not sure where else to turn. Tried searching for an answer to my problembut couldn't find anything.

So whatI'm trying to do is, in a single spreadsheet, when I double click a specificcell in column J it will copy the content into a cell in column A. What I wouldlike is that when I double click on let's say cell J10, it should copy thecontent into cell A3. If I then click on J20, it should copy into cell A4,followed by A5, A6, etc... in order. Right now, with the code I have in place,it's simply copying the content into column A (correct column), but it copiesonto the same row as the cell I double clicked, so instead of being in A3 whereI want it, it's in A10 (example). Currently, this is the code I have in place:

PrivateSub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Not Intersect(Target,Range("J:J")) Is Nothing Then
Cancel = True 'Cancels edit mode that is noramally invokedwith double click
Target.Copy Destination:=Cells(Target.Row,"A")
End If

End Sub

Hopethis is easy enough to understand. Any help would be appreciated.
Thanks!!

 

[Fluff]

Hi & welcome to MrExcel
Try

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

If Not Intersect(Target, Range("J:J")) Is Nothing Then
   Cancel = True 'Cancels edit mode that is noramally invokedwith double click
   Target.Copy Destination:=Cells(Rows.Count, "A").End(xlUp).Offset(1)
End If

End Sub

 

[CAMARD2]

That worked! Thank you so much!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[CAMARD2]

Ok, I have another question related to this.

As I said above, I have content in the J column that I want to have copied into column A (A2,A3, A4, etc) upon double-clicking. It's working fine with the code you've provided.


I now also have content in column M that I would liketo copy onto column C (in the same spreadsheet). Same set up as above.


Is it possible to do this if I already have a code set upwith columns J/A ?


Thanks in advance

 

[Fluff]

How about

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

If Not Intersect(Target, Range("J:J")) Is Nothing Then
   Cancel = True 'Cancels edit mode that is noramally invokedwith double click
   Target.Copy Destination:=Cells(Rows.Count, "A").End(xlUp).Offset(1)
ElseIf Not Intersect(Target, Range("M:M")) Is Nothing Then
   Cancel = True 'Cancels edit mode that is noramally invokedwith double click
   Target.Copy Destination:=Cells(Rows.Count, "C").End(xlUp).Offset(1)
End If

End Sub

 

[CAMARD2]

Yep, that worked. Thank you !!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[CAMARD2]

Bumping this thread as I have another request similar to this one that I posted a few weeks ago.


Currently I am using the code listed above in comment #6. I double click a cell in rows J or M and the contents are copied to rows A or C. Right now it is set up so that once copied to column A/C, it follows row by row, starting with A2 (or C2) and so on…

I’m just wondering if it’s possible to have the contents copied to a single cell in A/C, so instead of A2, A3, A4, etc… all of the copied content will be found in cell A2 (and C2), with each double click lending itself to a new line break or “paragraph” in the cell (alt+enter) and everything will still be separated nicely. So no erasing or overwriting the content that is already there.


I’m asking because as it stands, I sometimes need to copy the data in rows A/C into a single cell on another work sheet, but copying multiple cells into a single cell causes a whole mess of problems. Therefore copying the contents of one single cell to another will just make things so much easier.

Thanks in advance !

 

[Fluff]

How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Not Intersect(Target, Range("J:J")) Is Nothing Then
      Cancel = True 'Cancels edit mode that is noramally invokedwith double click
      Range("A2").Value = Range("A2").Value & Chr(10) & Target.Value
   ElseIf Not Intersect(Target, Range("M:M")) Is Nothing Then
      Cancel = True 'Cancels edit mode that is noramally invokedwith double click
      Range("C2").Value = Range("C2").Value & Chr(10) & Target.Value
   End If
End Sub