HowTo Copy data by a double click on a specific cell of one sheet to another sheet

Question

Hallo

I am writing down the requirements of what I want to do using a macro in excel:

• I have a workbook with 4 worksheets: The SourceSheet where I save my database, the TargetSheet where I want to send some products of the database, in order to do some calculations,the resultsSheet and the InfoSheet.
• If the user double clicks in a cell of the SourceSheet that belongs to column A e.g. A8, then the data of cells A8 to F8 should be copied to cells C18 to H18 of the TargetSheet and cell J8 of the sourceSheet should be copied to cell B18 of the TargetSheet. If cells B18 to H18 of the TargetSheet are occupied, then the data should be copied one row underneath.
• If the user double clicks in a cell of the SourceSheet that does not belong to Column A nothing should happen.
• If the user double clicks in any other sheet nothing should happen again.

What I am having trouble with is the one in bold letters above. I know that I have to use for sure the following:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

End Sub

Therefore I tried sth like the following in the SourceSheet (without success though :( )

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Range("ActiveCell:(ActiveCell.Offset(0, 5))").Copy Worksheets("TargetSheet ").Range("C2:G2")
    ActiveCell.Offset(0, 9).Copy Worksheets("TargetSheet ").Range("B18")
End Sub

I am newbie in vba so any help would be appreciated. Thansk in advance

Answer 1

You need to test whether you have actually double-clicked within the A:A range ( = column 1).

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

    If ActiveCell.Column = 1 Then
        MsgBox "Double click on A:A range."
    End If
End Sub