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