CODEWITHSUNDEEP
excelvba
Touminator
Touminator

Reputation: 9

Run macro on cell click with variable target row

I'm trying to get an automatic transmission of a variable row as soon as the cell of column H of that row is selected. So if the user klicks H5, I would want the entire row 5 being copied to my worksheet "Rechnung" in I17.

This poor piece of code doesn't do the trick, because the range of the copied row isn't defined properly:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Column = Range("H1").Column Then
    Range(Target.Row).Copy Destination:=Worksheets("Rechnung").Range("I17")
  End If
End Sub

Any suggestions?

Thanks and all the best, Thomas

Upvotes: 0

Views: 166

Answers (1)

BigBen
BigBen

Reputation: 49998

You can't copy an entire row and paste it into column 17... it won't fit.

Try using the Intersection of Target.EntireRow and the worksheet's UsedRange.

You can also use Intersect to check if Target includes a cell in column H:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Intersect(Target, Me.Range("H:H")) Is Nothing Then Exit Sub
  If Intersect(Target, Me.UsedRange) Is Nothing Then Exit Sub

  Intersect(Target.EntireRow, Me.UsedRange).Copy Destination:=Worksheets("Rechnung").Range("I17")
End Sub

Upvotes: 2

Related Questions