Hello, the code provided below is from my previous sucess (A long time ago) on this board and i'd like to ask for help again.
<tbody>
</tbody>
Assuming the above table starts in "A1" the below vba code should copy and paste any such doubleclicked cell into sheet2 (from columnA) or Sheet3 (from columnB).
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("a1:a10")) Is Nothing Then
Cancel = True
If Target.Row > 1 And Len(Target.Value) Then Worksheets("Sheet2").Range("a1").Value = Target.Value
Worksheets("Sheet2").Activate
ElseIf Not Intersect(Target, Range("b1:b10")) Is Nothing Then
Cancel = True
If Target.Row > 1 And Len(Target.Value) Then Worksheets("Sheet3").Range("a1").Value = Target.Value
Worksheets("Sheet3").Activate
End If
End Sub
Great! But now looking at the next table below, i need upon doubleclick - if such cell is text to copy to sheet2 and if cell is a number to copy to sheet3.
<tbody>
</tbody>
Any ideas or thoughts welcome, hopefully the above code works well for those who are interested in using it and/or building upon it.
Many thanks,
a | 1 |
b | 2 |
c | 3 |
d | 4 |
e | 5 |
f | 6 |
g | 7 |
h | 8 |
i | 9 |
j | 10 |
<tbody>
</tbody>
Assuming the above table starts in "A1" the below vba code should copy and paste any such doubleclicked cell into sheet2 (from columnA) or Sheet3 (from columnB).
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("a1:a10")) Is Nothing Then
Cancel = True
If Target.Row > 1 And Len(Target.Value) Then Worksheets("Sheet2").Range("a1").Value = Target.Value
Worksheets("Sheet2").Activate
ElseIf Not Intersect(Target, Range("b1:b10")) Is Nothing Then
Cancel = True
If Target.Row > 1 And Len(Target.Value) Then Worksheets("Sheet3").Range("a1").Value = Target.Value
Worksheets("Sheet3").Activate
End If
End Sub
Great! But now looking at the next table below, i need upon doubleclick - if such cell is text to copy to sheet2 and if cell is a number to copy to sheet3.
a | 1 |
b | 2 |
c | 3 |
d | 4 |
5 | e |
6 | f |
7 | g |
h | 8 |
i | 9 |
j | 10 |
<tbody>
</tbody>
Any ideas or thoughts welcome, hopefully the above code works well for those who are interested in using it and/or building upon it.
Many thanks,