Macro to copy from Sh1 (triggered by an entry), paste in Sh2 but also integrating it into existing code

[Raceman]

Hi: I need help with writing a new macro and integrating it into some existing code. My existing code works well for creating an auto hyperlink, from one sheet to another, triggerered by a dropdown value in Sh1 column K.

I'm trying to add vba code to have the same trigger do a copy of cells in column A and B from Sh1 and paste to Sh2. The info from these 2 cells will go into Sh2 A and B on the same row as the hyperlink points to (which is the next available open Cell in column A).


Sh1 is named MAD_CAT
Sh2 is named CABLE_MATRIX

Here is my tested hyperlink code :

'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

 

[wigi]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rws As Long
    If Target.Count = 1 And Target.Column = 11 Then
        If InStr(Target, "Cable Assy") > 0 Then
            Rws = Worksheets("CABLE_MATRIX").Cells(Rows.Count, "A").End(xlUp).Row + 1
            ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(, 5), Address:="", _
                        SubAddress:="CABLE_MATRIX!A" & Rws, TextToDisplay:="CABLE_MATRIX!A" & Rws
            Sheets("CABLE_MATRIX").Range("A" & Rws).Resize(, 2).Value = Target.Offset(, 1 - Target.Column).Resize(, 2).Value
        End If
    End If
End Sub

 

[Raceman]

Thank you for your help, I apologize about the cross posting...I was in such a hurry I didn't review the rules well enough. And was just trying to maximize visability into my problem. Your solution works well, I truely appreciate it.

 

[Raceman]

Your code works well, can I add a slight wrinkle to it? When the copy and paste from Sh1 A and B to Sh2 A and B occurs, can you show me how to modify the code to enter the word "Combined" in Column C same row?

 

[JLGWhiz]

This should now add the word combined to the destination sheet, col. C

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rws As Long
    If Target.Count = 1 And Target.Column = 11 Then
        If InStr(Target, "Cable Assy") > 0 Then
            Rws = Worksheets("CABLE_MATRIX").Cells(Rows.Count, "A").End(xlUp).Row + 1
            ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(, 5), Address:="", _
                        SubAddress:="CABLE_MATRIX!A" & Rws, TextToDisplay:="CABLE_MATRIX!A" & Rws
            Sheets("CABLE_MATRIX").Range("A" & Rws).Resize(, 2).Value = Target.Offset(, 1 - Target.Column).Resize(, 2).Value
     Sheets("CABLE_MATRIX").Range("C" & Rws) = "Combined"
        End If
    End If
End Sub

 

[Raceman]

Fantastic! thank you so much!