PDA

View Full Version : Filter/sort columns in another sheet



lfalla
09-13-2016, 01:36 PM
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
09-14-2016, 03:33 AM
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
09-14-2016, 10:00 AM
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
09-14-2016, 08:44 PM
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