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!!

 

[CAMARD2]

Woks perfectly. Thanks so much!!!

 

[Fluff]

You're welcome

 

[feni1388]

Hello.... I have similar problem... but I need to copy it to another worksheet instead of the same worksheet and different column or row.
I tried using the formula below but it didn't work.

Public Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim c As Range
Set c = ActiveCell
Worksheets("Data").Cells(Rows.Count, "W").End(xlUp).Offset(1).Value = c
Worksheets("Data").Select
End Sub

But if I use the following formula it'll go to only a specific cell and I can't go to the next blank cell.

Public Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim c As Range
Set c = ActiveCell
Worksheets("Data").Cells(6, 1).Value = c
Worksheets("Data").Select
End Sub

Can anyone please help?

Thank you in advance.

 

[Fluff]

In what way doesn't your code work?

 

[feni1388]

Hello...
When I double click it nothing happens....

Sorry, but I think I explained it wrong.
When I double click it, I want to it to go to the next next blank cell because of the "Total" row that should indicate the end of the input for the first company.

I uploaded an image for you to easy understand what I wanted to do.

I'm so bad at macro code, so if you can help me that'd be great.
thanks in advance.

 

[Fluff]

How about

Public Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Column > 1 Then Exit Sub
   Cancel = True
   Worksheets("Data").Cells(Rows.Count, "O").End(xlUp).Offset(1, 3).Value = Target.Value
   Worksheets("Data").Select
End Sub

 

[feni1388]

It works great! ... thank you so much....

 

[Fluff]

You're welcome & thanks for the feedback.