VBA - Help building upon "copy & paste to new sheet cell on doubleclick" with isnumber & istext

[Zetroza]

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.

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,

 

[James006]

Hi,

You can test the following :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' Text goes to Sheet 2 and Number goes to sheet 3
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:B10")) Is Nothing Then Exit Sub


  If IsNumeric(Target) Then
    Worksheets("Sheet3").Range("a1").Value = Target.Value
    Worksheets("Sheet3").Activate
  Else
    Worksheets("Sheet2").Range("a1").Value = Target.Value
    Worksheets("Sheet2").Activate
  End If


Cancel = True


End Sub

HTH

 

[Zetroza]

Fantastic - that works as requested,
Furthermore, your code works assuming the columns are next to each other (a1:b10 as a single range) however how can I edit this for seperate columns.

EG: a1:a10 & e1:e10 and not in between

I've attempted editing your code but I can not find a way around this one.
Many thanks again,

 

[James006]

Glad you could fix your problem ...

For the modification, you can test : If Intersect(Target, Range("A1:A10,E1:E10")) Is Nothing Then Exit Sub

HTH

 

[Zetroza]

thanks, that alteration yet so simple in hindsight has had me running lots of "work around" code in the past.
Cheers again

 

[James006]

You are welcome ...

We are all embarked on a journey of continuous learning ...!!!

Cheers

 

[Zetroza]

After huge sucess with the both the above pieces of code and various ways i've manipulated it ever so slightly i've reached beyond my abilities in trying to create a dynamic destination cell.

How can we make the destination cell "dynamic" in a way that:-
*Upon first activation (or if destination cell blank), the destination cell is A1,
*Second, third activation, the destination cell is B1, C1, etc

Is it even possible,
any small hints or tips let me know,
thanks,

 

[James006]

Hello,

You could test the following modification ...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' Text goes to Sheet 2 and Number goes to sheet 3
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:B10")) Is Nothing Then Exit Sub
Dim lsh2 As Long
Dim lsh3 As Long


lsh3 = IIf(Worksheets("Sheet3").Range("A1") = "", 1, Worksheets("Sheet3").Range("a65536").End(xlUp).Row + 1)
lsh2 = IIf(Worksheets("Sheet2").Range("A1") = "", 1, Worksheets("Sheet2").Range("a65536").End(xlUp).Row + 1)




  If IsNumeric(Target) Then
    Worksheets("Sheet3").Cells(lsh3, 1).Value = Target.Value
    Worksheets("Sheet3").Activate
  Else
    Worksheets("Sheet2").Cells(lsh2, 1).Value = Target.Value
    Worksheets("Sheet2").Activate
  End If


Cancel = True


End Sub

HTH

 

[Zetroza]

I have had a huge amount of fun today working with the above code and really appreciate your support and response.
Furthermore, I feel I am missing a trick by not combing the above 2 pieces of code so that:-

upon activation,
isnumber to sheet3 - remains dynamic
else
istext to sheet2 - becomes an absolute single cell destination. EG: A1

Any futher comments or guidance would be greatly appreciated,
Many thanks,

 

[James006]

Hello,

Not sure to fully understand your latest message ...

1. Do you get the dynamic destination cell you were looking for ...?
2. If you only need this feature for sheet 3 ... and not for sheet 2 .... just replace lsh2 by 1 ...

HTH