Using Vba (buttons/list) to filter sheets

[Vexorg]

Hello,

I'm basically trying to put the the menu that you get when filtering a column on a separate sheet (list + checkboxes). Im providing an extract of data to multiple teams and each have their own name and associated data.

I have 2 sheets in the excel file. They are called

1. 3rd Party and Phones
2. Coverage Teams and TNs

I originally came up with the following and planned on making buttons for each team but there are 71 teams, I would have to make 71 buttons. (this would be only 2 buttons)

Sub Filter()
'
' Filter sheet 3rd Party & Phones
'

'

    Rows("1:1").Select
    Range("AZ1").Activate
    Selection.AutoFilter
    Sheets("3rd Party and Phones").Range("$A$1:$BK$19814").AutoFilter Field:=55, Criteria1:= _
        "Baltimore CMG"
    

' Filter sheet Coverage Teams and TNs
'

'
    Sheets("Coverage Teams and TNs").Range("$A$1:$C$3890").AutoFilter Field:=3, Criteria1:= _
        "Baltimore CMG"
End Sub

Sub FEPB_DA()

    Rows("1:1").Select
    Range("AZ1").Activate
    Selection.AutoFilter
    Sheets("3rd Party and Phones").Range("$A$1:$BK$19814").AutoFilter Field:=55, Criteria1:= _
        "FEPB DA"

' Filter sheet Coverage Teams and TNs
'

'
    Sheets("Coverage Teams and TNs").Range("$A$1:$C$3890").AutoFilter Field:=3, Criteria1:= _
        "FEPB DA"
End Sub

In addition I would have to keep hard coding the team names. think there is a use a predefined list so it could check for the next subsequent value but im not sure how..

Can someone help with this?

Also how could you create a list/buttons for these values (team names)?

 

[Vexorg]

Ok i did a little more research and i see that i can simply create a listbox. Now my question is how do i filter my 2 sheets based on the criteria i define in the list box?

 

[Vexorg]

Ok I've refined my approach to this. In Sheet1 I now have a listbox (it contains the values I want to filter by). When I select an item in the list box it appears in cell G6.

How can I make my other 2 sheets filter by the value in G6, when they are selected from the listbox.

Sheet Names:

1. "3rd Party and Phones" (filter by column BC)
2. "Coverage Teams and TNs" (filter by column C)

Code I currently have:

This has the values hard coded in, how can i add Sheet1 Cell G6 instead of "Baltimore CMG" ?

Sub Filter()
'
' Filter sheet 3rd Party & Phones
'

'
    Rows("1:1").Select
    Range("AZ1").Activate
    Selection.AutoFilter
    Sheets("3rd Party and Phones").Range("$A$1:$BK$19814").AutoFilter Field:=55, Criteria1:= _
        "Baltimore CMG"
    

' Filter sheet Coverage Teams and TNs
'

'
    Sheets("Coverage Teams and TNs").Range("$A$1:$C$3890").AutoFilter Field:=3, Criteria1:= _
        "Baltimore CMG"
End Sub

Thanks!