Filter/sort columns in another sheet

[lfalla]

First of all, sorry if this is super basic. I swear that I've searched extensively about how to solve this problem but I couldn't find any answer. Also, since my VBA skills are nearly zero, I'm not able to solve it by myself.

I have a spreadsheet with several sheets. Number one sheet is some sort of index. The thing is: I would love to create buttons (in sheet #1) that could make possible to (1) filter/sort the information in another sheet (for example, sheet #2) and then (2) show sheet #2, with the filtered/sorted information.

There is no need to copy, no need to delete, anything, just a button that if pressed tossed me to the specific sheet with a specific information showing first and or filtered.

I am able to make "hyperlink" buttons (with VBA, yay), but I can't find any way to add the prior part of filtering/sorting. Everytime I tried to add another code that I found to filter or sort the code would crash and once again I would get stucked.

If anyone happen to have any insight or reference text for me I would greatly appreciate.

Thanks a lot and sorry my english.

[mana]

Sheet1 layout
---A--- 　---B--- 　---C---
1 No.　　　Name　　age
2 1　　　　a　　　　35
3 2　　　　b　　　　42
4 3　　　　c　　　　25
5 4　　　　d　　　　45
6 5　　　　e　　　　37


Sheet2 layout
-A-　　　-B-　　-C-　　-D-　　-E-
1 No.　name　　age　　　　　age
2　　　　　　　　　　　　　　 >40

Option Explicit


Sub test()
    Dim tbl As Range
    Dim crt As Range
    Dim ext As Range


    Set tbl = Worksheets("Sheet1").Range("A1").CurrentRegion
    Set crt = Worksheets("Sheet2").Range("E1:E2")
    Set ext = Worksheets("Sheet2").Range("A1:C1")
   
    tbl.AdvancedFilter xlFilterCopy, crt, ext
    ext.CurrentRegion.Sort Key1:=ext.Columns(3), order1:=xlDescending
   
End Sub

[lfalla]

Hello. Before anything, thank you very much for your response, mana.

I have tried your code, but I seem to be failing somewhere. Also, it seems that I missed some points in my explanation. The idea was to make Sheet#1 named Index and contain buttons to filter information in "major areas" (for example - major area, button: cakes). Each subsequent sheet would be the major area where would contain information in, I'd say, 4 columns, with the last one being the "qualifier" column.

Example:

Sheet#1
-A-
Desserts

Cakes
Biscuits
Pies
Ice Creams

While Sheet#2 would be something along those lines:
-A B - C - D -
Name Recipe Ingredientes Type
Cake A zzz yyyy Cakes
Biscuit A aaaaa bbbb Biscuits
Biscuit B aaaaa bbbbb Biscuits
Cake B zzz yyyy Cakes

When pressing the button "Cakes" in Sheet#1 would sort or activate the autofilter in Sheet#2 (while "transporting" the reader to Sheet#2). The same would happen if pressed "Biscuits", for example. I am capable enough to make the "transporting" part (with Worksheet="Sheet2".Activate), but I'm still ignorant in how to make the sorting or autofilter part, or how to make your code work under that idea.

Thank you!

[mana]

instead of button click

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim tbl As Range
    
    If Target.Column <> 1 Then Exit Sub
    If Target.Row < 4 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    
     Set tbl = Worksheets("Sheet2").Range("a1").CurrentRegion
     
     With tbl
        .AutoFilter
        .AutoFilter field:=.Columns("d").Column, Criteria1:=Target.Value
        .Sort Key1:=.Columns("b"), Header:=xlYes
        .Parent.Activate
     End With
     
     Cancel = True


End Sub