Filtering multiple tables at once via a search box on excel

Question

I have an excel workbook with 13 sheets (one for each month in the year + a main sheet) and an identical table of employee stats in each sheet. The employee stat table is named after it's corresponding month and has identical column headers. I have a dashboard on the main sheet that has charts pulling from the other tables.

I want to create a searchbox that will allow you to filter all of the tables in their respective worksheets at once

Here is the line of code that I can't figure out. I am attempting to make the filter range refer to more than one table.

 Set DataRange = sheets.ListObjects("January""February""March").Range 

Is there any way to code the searchbox to refer to multiple tables in multiple sheets? I would need to identify the worksheet name, and the table name. I'm not sure how to do this

For reference, January is the table in the sheet titled "Jan".

Here is the full code I use:

 Sub SearchBox()
  Dim dict as Object
  Set dict = CreateObject("Scripting.Dictionary")

 Dim i as Long
 For 1 = 3 to 14
 Set dict(i) = Worksheets(i).ListObjects(1).Range

   Dim myButton As OptionButton
   Dim MyVal As Long
   Dim ButtonName As String
   Dim sht As Worksheet
   Dim myField As Long
   Dim DataRange As Range
   Dim mySearch As Variant

  'Load Sheet into A Variable
   Set sht = ActiveSheet

 'Unfilter Data (if necessary)
  On Error Resume Next
  sht.ShowAllData
  On Error GoTo 0

 'Filtered Data Range (include column heading cells)

   dict(i).Autofilter_

   Field:=myField,_
   Criteria1:="=*" & mySearch & "*", _
   Operator:=xlAnd

   Next

  'Retrieve User's Search Input
   mySearch = sht.Shapes("StaffLookUp").TextFrame.Characters.Text 'Control Form


   'Loop Through Option Buttons
    For Each myButton In ActiveSheet.OptionButtons
    If myButton.Value = 1 Then
    ButtonName = myButton.Text
    Exit For
      End If
     Next myButton

  'Determine Filter Field
   On Error GoTo HeadingNotFound
   myField = Application.WorksheetFunction.Match(ButtonName,          DataRange.Rows(1), 0)
  On Error GoTo 0

  'Filter Data
   DataRange.AutoFilter _
     Field:=myField, _
     Criteria1:="=*" & mySearch & "*", _
     Operator:=xlAnd

  'Clear Search Field
   sht.Shapes("UserSearch").TextFrame.Characters.Text = "" 'Control Form


Exit Sub

'ERROR HANDLERS
HeadingNotFound:
 MsgBox "The column heading [" & ButtonName & "] was not found in cells " &        DataRange.Rows(1).Address & ". " & _
 vbNewLine & "Please check for possible typos.", vbCritical, "Header Name Not Found!"

 End Sub

Answer 1

You should be able to reference the ListObjects by name using Range(Name).

I extracted the filter process into it's own sub. I also added an optional parameter ClearFilters. This will give you the option to stack the filters.

Sub ApplyFilters()
    Dim FieldName As String, mySearch As Variant
    Dim myButton As OptionButton
    Dim m As Integer

    For Each myButton In ActiveSheet.OptionButtons
        If myButton.Value = 1 Then
            FieldName = myButton.Text
            Exit For
        End If
    Next myButton

    mySearch = ActiveSheet.Shapes("StaffLookUp").TextFrame.Characters.Text    'Control Form
    mySearch = "=*" & mySearch & "*"

    For m = 1 To 12

       FilterTable MonthName(m), FieldName, mySearch, True

    Next

End Sub


Sub FilterTable(TableName As String, FieldName As String, mySearch As Variant, Optional ClearFilters As Boolean = False)
    Dim DataRange As Range, FilterColumn As Integer

    On Error Resume Next
    Set DataRange = Range(TableName)
    On Error GoTo 0

    If DataRange Is Nothing Then
        MsgBox TableName & " not found"
        Exit Sub
    End If

    If ClearFilters Then
        On Error Resume Next
        DataRange.Worksheet.ShowAllData
        On Error GoTo 0
    End If

    On Error Resume Next
    FilterColumn = DataRange.ListObject.ListColumns(FieldName).Index
    On Error GoTo 0

    If FilterColumn = 0 Then
        MsgBox TableName & ": " & FieldName & " not found"
        Exit Sub
    End If

    DataRange.AutoFilter _
            Field:=FilterColumn, _
            Criteria1:=mySearch, _
            Operator:=xlAnd

End Sub

Answer 2

Is there any way to code the searchbox to refer to multiple tables in multiple sheets?

Nope. As you may have gathered somewhat, your attempt doesn't work, but it's enough that I think I understand what you're trying to do:

Set DataRange = sheets.ListObjects("January""February""March").Range

For a number of reasons it's invalid syntax (as you're probably aware) but you also can't assign a range that spans multiple worksheets. What you're looking for is going to be done in a loop, instead of 1 range you're going to have 13 ranges (or however many you need).

I would need to identify the worksheet name, and the table name. I'm not sure how to do this

You can refer to the sheets by Name or Index, e.g.:

Worksheets(1) '## Refers to the first sheet in the book

Or:

Worksheets("January") '## Refers to worksheet named 'January' or raise error if sheet name doesn't exist

What you'll need to do is a For/Each loop, and handle each sheet successively, if you want a handle on all of their respective ranges, dump them in to a collection or dictionary:

Dim dict as Object
Set dict = CreateObject("Scripting.Dictionary")

Dim i as Long
For i = 1 to 13 'Modify as needed
    '## Assumes only 1 ListObject table on each sheet; if there are multiple,
    '  you should refer to the ListObjects by name instead of index
    Set dict(i) = Worksheets(i).ListObjects(1).Range
Next

Then, later you would apply the filter in a similar loop. Since the dict Values are ranges, you can work directly with the dict object like so:

For i = 1 to 13
    dict(i).AutoFilter ...


Next