Clicking on a cell in Range to fill Another single cell Cell VBA

[AoS80]

Hello,

I am new to VBA and I am trying to type a code that allows me when I click on a cell within range for example A1:A20 it will copy that cell to C1

Also, lets say i want to do this multiple times in the same sheet. For example I also want to click on a cell within another range for example H1:H40 and it will copy that cell to D1

Please note that I am using Excel 2016

Appreciate your support in advance.

 

[James006]

Hi,

You could test

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("A1:A20")) Is Nothing Then Exit Sub
Range("C1").Value = Target.Value
Cancel = True
End Sub

 

[James006]

For your two ranges

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("A1:A12,H1:H40")) Is Nothing Then Exit Sub
    If Target.Column = 1 Then
        Range("C1").Value = Target.Value
    Else
        Range("D1").Value = Target.Value
    End If
Cancel = True
End Sub

 

[AoS80]

Both are working perfectly! that's great James. Thank you for your support.

 

[James006]

You are welcome

 

[AoS80]

You are welcome

Just an update. I used the below code and the end result was when i double click anything from AA4:AA42 it copies the value to G2. However, when i click anything in the second range AB4:AB42 it replaces G2 instead of copying to G4. I tried to work around it but without any success. If you would take another look would really appreciate it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("AA4:AA42,AB4:AB42")) Is Nothing Then Exit Sub
If Target.Column = 1 Then
Range("G4").Value = Target.Value
Else
Range("G2").Value = Target.Value
End If
Cancel = True
End Sub

 

[James006]

If my understanding is correct ...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("AA4:AA42,AB4:AB42")) Is Nothing Then Exit Sub
If Target.Column = 27 Then
Range("G4").Value = Target.Value
Else
Range("G2").Value = Target.Value
End If
Cancel = True
End Sub

 

[AoS80]

If my understanding is correct ...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("AA4:AA42,AB4:AB42")) Is Nothing Then Exit Sub
If Target.Column = 27 Then
Range("G4").Value = Target.Value
Else
Range("G2").Value = Target.Value
End If
Cancel = True
End Sub

You are Amazing! it so perfect doing exactly what i needed.

Thanks!

 

[James006]

You are welcome