Filtering multiple tables at once via a search box on excel
<p>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. </p>

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

<p>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.</p>

<pre><code> Set DataRange = sheets.ListObjects("January""February""March").Range 
</code></pre>

<p>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</p>

<p>For reference, January is the table in the sheet titled "Jan". </p>

<p>Here is the full code I use: </p>

<pre><code> 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:="=*" &amp; mySearch &amp; "*", _
   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:="=*" &amp; mySearch &amp; "*", _
     Operator:=xlAnd

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


Exit Sub

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

 End Sub
</code></pre>