In the code below, the destination sheet is called Sheet2 and the values will be pasted in the A column. Look up Sheet2 and A to change them to suit your needs:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Target.Value
To implement the code: right-click the sheets tab you want this to work for (so not Sheet2) and select view code. Paste the code in the big white field.