Hello: I need help in completing a macro and incorporating it into some existing code that does function as designed. It's close to working but I haven't been able to get it right.
The 1st part of the code adds a hyperlink (and it works properly) see below
The 2nd part is the problem, I'm trying to trigger a copy and paste event with the same trigger as is done with the hyperlink (above), it is triggered when a selection is made in the active sheet column K and the dropdown selection of "Cable Assy" is made. I'm trying to copy Active row cell A and B and paste them in A and B on Sh2 (CABLE_MATRIX) into the next blank row. Which also happens to be the same row as what the automatic hyperlink attached to.
Note: my code that attempts to do what I am trying to do is currently set to copy only A. It doesn't fail, but it copies and pastes the dropdown info from K to cell A on the CABLE_MATRIX sheet.
Thanks I would really appreciate your help on this
The 1st part of the code adds a hyperlink (and it works properly) see below
The 2nd part is the problem, I'm trying to trigger a copy and paste event with the same trigger as is done with the hyperlink (above), it is triggered when a selection is made in the active sheet column K and the dropdown selection of "Cable Assy" is made. I'm trying to copy Active row cell A and B and paste them in A and B on Sh2 (CABLE_MATRIX) into the next blank row. Which also happens to be the same row as what the automatic hyperlink attached to.
Note: my code that attempts to do what I am trying to do is currently set to copy only A. It doesn't fail, but it copies and pastes the dropdown info from K to cell A on the CABLE_MATRIX sheet.
HTML:
'This is option A to add hyperlink automatically. When "Cable Assy..." in column k dropdownn is selected then
'a hyperlink is automatically created which links to the next blank cell A on the Cable Matrix Worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rws As Long, sh As Worksheet
Set sh = Worksheets("CABLE_MATRIX")
With sh
Rws = .Cells(Rows.Count, "A").End(xlUp).Row + 1
End With
If Target.Count = 1 And Target.Column = 11 Then
If InStr(Target, "Cable Assy") <> 0 Then
ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(0, 5), Address:="", SubAddress:= _
"CABLE_MATRIX!A" & Rws, TextToDisplay:="CABLE_MATRIX!A" & Rws
End If
End If
'This code automatically copies info from active cells A and B to CABLE_MATRIX Sheet with same trigger as above
'(when "Cable Assy" is selected in dropdown cell K in Sheet "MAD_CAT -which is the active sheet)
Dim fVal As Range
If Not Intersect(Target, Range("K:K")) Is Nothing Then
Set fVal = Sheets("CABLE_MATRIX").Range("A:A").Find(Range("A" & Target.Row).Value, LookIn:=xlValues)
Target.Resize(1, 2).Copy Sheets("CABLE_MATRIX").Range("A" & fVal.Row)
End If
Thanks I would really appreciate your help on this