Worksheets.range variable vba

Tayob

New Member
Joined
Feb 18, 2022
Messages
2
Office Version
  1. 2021
  2. 2019
Platform
  1. Windows
Hi, I am trying to copy certain row values to another worksheet based on which row the user clicked in Column A. For example if user clicks Cell A3, i want to be able to copy the contents of cells D3, E3, G3 to another worksheet. This code works if i know the user will click A136 but i need this to be a variable range so that whichever cell in Column A is pressed, the copy will happen

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("A136")) Is Nothing Then

Worksheets("Sheet1").Range("D136:D136").Copy _
Destination:=Worksheets("Sheet2").Range("B2")
Worksheets("Sheet1").Range("E136:E136").Copy _
Destination:=Worksheets("Sheet2").Range("B3")
Worksheets("Sheet1").Range("G136:G136").Copy _
Destination:=Worksheets("Sheet2").Range("B4")

End If
End If
End Sub


Any ideas or suggestions on how to get pass this.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,799
Office Version
  1. 2016
Its not too difficult.
But, which cell in sheet2 to be pasted, if click on next cell in column A sheet1 , i.e, A137?
B5,B6,B7 (downward) or C2,C3,C4(accross)?
 

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,799
Office Version
  1. 2016
Try this in sheet1 code:
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count > 1 And Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    With Worksheets("Sheet2")
        .Range("B2").Value = Target.Offset(0, 3).Value
        .Range("B3").Value = Target.Offset(0, 4).Value
        .Range("B4").Value = Target.Offset(0, 6).Value
    End With
End Sub
 
Solution

Tayob

New Member
Joined
Feb 18, 2022
Messages
2
Office Version
  1. 2021
  2. 2019
Platform
  1. Windows
Try this in sheet1 code:
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count > 1 And Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    With Worksheets("Sheet2")
        .Range("B2").Value = Target.Offset(0, 3).Value
        .Range("B3").Value = Target.Offset(0, 4).Value
        .Range("B4").Value = Target.Offset(0, 6).Value
    End With
End Sub
Thank You for the speedy reply and solution. Your code is working 100%.
 

Forum statistics

Threads
1,176,169
Messages
5,901,737
Members
434,915
Latest member
MyLadysKnight

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top