sidatra79 36 Junior Poster

Hi everybody,
I am rather new to VBA programming in EXCEL and I could use some of your help.
I want to do the following:
In my excel file there are two sheets: SourceSheet and DestSheet.
In SourceSheet there are data filling columns A to I and starting from the 2nd row and till 1500th row. I wish that when I "double click" on e.g. cell A100, then all data from cell A100 till cell E100 of SourceSheet are copied in cell B22 till F22 of DestSheet. Then If I click on e.g. cell A54 of the SourceSheet, then all data from cell A54 till cell E54 of SourceSheet should be copied in the DestSheet one row below:cellsB23 till F22. The double clicking should cause this copy only when is done in one of the A2-A1500 cells.
I managed this partially with the following code:

Sub SendToDestSheet ()
    Dim i
    Dim SMenge As Object
    Set SMenge = Application.Intersect(Range("A:e"), Range(ActiveCell.Address)) 
    If SMenge Is Nothing Then Exit Sub
    Selection.Copy
    Sheets("DestSheet ").Select
    Range("B24").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End Sub

The problem:
My code works if I assign first the above macro in a button, then mark the cells in the SourceSheet and finally click the button. But I would like it work by double clicking in any of the A cells (A2 til A1500) cells of the SourceSheet without using any button.

Any idea???
Thanks everybody in advance for your time :D

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.