I think this will do the trick for you. Put this in the sheet's code section
for the sheet where you want to do the double-click trick. Hopefully code is
commented well enough so you can see what you need to change for your
real-world setup.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
'How to insert code into worksheets:
'
http://www.jlathamsite.com/Teach/WorksheetCode.htm
'(e-mail address removed)
'name of Sales Record Sheet
Const Destination = "Sheet2" '<- Change
'Column ID where to paste on the
' Sales Record Sheet to receive the copy
Const DestColumn = "A" '<- change?
'Column ID to be double-clicked to cause move
Const DblClkColumn = "A" '<- Change?
Dim DestRange As Range
If Target.Cells.Count > 1 Then
'not sure how, but if...
Exit Sub
End If
If Target.Column <> Range(DblClkColumn & Target.Row).Column Then
Exit Sub
End If
'copy from column A to last used cell in row
Range("A" & Target.Row & ":" & _
Range("IV" & Target.Row).End(xlToLeft).Address).Copy
'determine where to paste on destination sheet
Set DestRange = Worksheets(Destination).Range(DestColumn & _
"65536").End(xlUp).Offset(1, 0)
'paste all - could use xlPasteValues here instead
DestRange.PasteSpecial xlPasteAll
Cancel = True ' cancel actual double-click action
Application.CutCopyMode = False
End Sub