Code to run macros from drop downlist, but undo if another macro is run.

[Elnicko]

Hi all I started using vba yesterday and im trying to edit this code.

I have 1000 cells with dropdown lists in them in colum I, this needs to be applied to all of them. (their all the same)

And If a different value is selected I need it to undo the last Marco.

Is this possible

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$9" Then
If Target = "Paul" Then
MacroA
ElseIf Target = "Rachel" Then
MacroB
ElseIf Target = "Julija" Then
MacroC
ElseIf Target = "Sue" Then
MacroD

End If
End If
End Sub

 

[Elnicko]

I'll be honest I don't completely understand how it works.
But it works and its nothing short of amazing!!

I have only ever used basic functions in Excel and this is a new world for
me nothing short of exciting. I see I have a whole lot to learn.

Thankyou so much.

If you don't mind can I ask you one last question?

Is it possible to have the marco place a link in cell m to where the data is pushed to?

 

[Elnicko]

.

 

[AlphaFrog]

                    With ThisWorkbook.Worksheets(Target.Text)
                        lNextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                        .Range("A" & lNextRow).Value = Target.Offset(, -1).Value
                        .Range("E" & lNextRow).Value = Target.Offset(, -3).Value & Target.Offset(, -2).Value
                        .Range("F" & lNextRow).Value = Target.Offset(, 1).Value
                        .Range("Z" & lNextRow).Value = Target.Address(0, 0)
                        [COLOR="Green"]'Hyperlink[/COLOR]
[COLOR="Red"]                        ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(, 4), _
                                                   Address:="", _
                                                   SubAddress:="'" & Target.Text & "'!" & Rows(lNextRow).Address, _
                                                   TextToDisplay:="Row " & lNextRow[/COLOR]
                    End With

 

[Elnicko]

Works like a charm.
Thank you for all your help.

I'm amazed at the speed you have come up with these solutions.
Time for me to do some tutorials I think.

Have a great day

 

[Elnicko]

Is there anyway to update the link if the row where the data is pushed to is moved?

 

[AlphaFrog]

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim lNextRow As Long, bSheetExists As Boolean
    
    If Target.Count = 1 And Target.Column = 9 Then
        If Target.Row >= 9 And Target.Row <= 1000 And Target.Row / 3 = Int(Target.Row / 3) Then
            On Error Resume Next
                ' Delete Named Range if exists (Undo last)
                Range("Link" & Target.Address(0, 0)).EntireRow.Delete
                ' Test if Worksheet from drop down list exists
                bSheetExists = Sheets(Target.Text).Name <> ""
            On Error GoTo 0
            If Target.Value = "" Then
                ' Clear previous hyperlink
                Target.Offset(, 4).ClearContents
                Target.Offset(, 4).Hyperlinks.Delete
            Else
                ' Log values and create link
                If bSheetExists Then
                    ' Log to selected sheet
                    With ThisWorkbook.Worksheets(Target.Text)
                        lNextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                        .Range("A" & lNextRow).Value = Target.Offset(, -1).Value
                        .Range("E" & lNextRow).Value = Target.Offset(, -3).Value & Target.Offset(, -2).Value
                        .Range("F" & lNextRow).Value = Target.Offset(, 1).Value
                    End With
                    ' Create Named Range and Hyperlink
                    ActiveWorkbook.Names.Add Name:="Link" & Target.Address(0, 0), RefersToR1C1:="='" & Target.Text & "'!R" & lNextRow
                    Target.Offset(, 4).Formula = "=""Row ""&ROW(Link" & Target.Address(0, 0) & ")"
                    ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(, 4), _
                                               Address:="", _
                                               SubAddress:="Link" & Target.Address(0, 0), _
                                               ScreenTip:="Click to follow."
                Else
                    MsgBox "Cannot locate a worksheet named " & Target.Text, vbExclamation, "Sheet Does Not Exist"
                End If
            End If
        End If
    End If
End Sub

 

[Elnicko]

Works great thanks!

But If I sort and filter it doesn't carry the named range?
Is there any way to do this?