VBA to adjust column filter, based on dropdown selection?

[DarkJester89]

Sheet 1, data with filters

Sheet 2, Dropdown with name range of filter data

Respectfully requesting how to activate filter based on dropdown, to include "All Regions", which would reset the filter to count all.

Workbook

Had assistance with this code with a warning of " code for updating the range of the filter itself " but it keeps throwing up 1004.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("M2")) Is Nothing Then
        Select Case Target.Value
            Case "All Regions"
                ActiveSheet.Range("$G$1:$G$134").AutoFilter Field:=1
            Case Else
                ActiveSheet.Range("$G$1:$G$134").AutoFilter Field:=1, Criteria1:=Target.Value
        End Select
    End If
End Sub

Thank you in advance.

 

[Fluff]

Is col G part of a table?
If so what is it's name?

 

[DarkJester89]

"Table_owssvr"

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("M2")) Is Nothing Then
      With ActiveSheet.ListObjects("table_owssvr")
         Select Case Target.Value
            Case "All Regions"
               If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
            Case Else
            .Range.AutoFilter .ListColumns("Region").Index, Target.Value
         End Select
      End With
   End If
End Sub

 

[DarkJester89]

It works! If I was trying to adjust Range "M1" (moving the dropdown to another sheet), how could I could the new location if it's on another sheet?

Would a change like that force ListColumns to need an update as well? thank you so much!

 

[Fluff]

You would need to put the code in the sheet with the dropdown & replace ActiveSheet with the name of the sheet with the table.