VBA auto sort triggered from different sheet

[SuperSoupGuy]

Hi all,

I'm not able to solve this problem using existing threads on this forum so here's the thing:

I have a data on "sheet4" which I would like to sort automatically (descending by column C, data starts at row 4) triggered by changing selection from drop down list placed in "B5" on "sheet2".

Right now I have this code (in worksheet code) which sorts the data but only when data in column C on sheet4 are changed:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Range("C3").Sort Key1:=Range("C4"), _
Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub

Do you please have any idea on how to change it and where to insert it? (which worksheet, new module, etc...) My VBA skills are limitally close to zero.

 

[Fluff]

Hi, welcome to MrExcel
How about this

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("B5")) Is Nothing Then Exit Sub
    Sheets("[COLOR=#ff0000]Sheet4[/COLOR]").Range("C3").Sort Key1:=Sheets("[COLOR=#ff0000]Sheet4[/COLOR]").Range("C4"), _
        Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
        
End Sub

Delete your existing code & place this in the sheet2 module. This assumes that the sheet to be sorted is called Sheet4 (ie that is the name on the tab)

 

[SuperSoupGuy]

Magic!

Thanks a lot for welcome and such an effective and elegant solution Fluff.

 

[Fluff]

Glad to help & thanks for the feedback