Copy Cell Data On Clicking

[rajushaikh]

I have a sheet1 and sheet2
If I click on cell a1 in sheet1 then data should be copied to sheet2 a1(not formula)
then I click on cell a3 in sheet1 then data in a3 should copied to next blank cell in column a of sheet2 that is sheet2!a2 not sheet2!a3

 

[My Aswer Is This]

Try this script.
You need to double click on any cell in Column (1)
This script assumes you have headers in both sheets

To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste this code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets(2).Rows(Lastrow).Value = Sheets(1).Rows(Target.Row).Value
End If
End Sub