Author: Oscar Cronquist Article last updated on January 09, 2018

Here is how to copy filter criteria from an excel table and use the same table filters on another table. Table1 is on sheet1 and table2 is on sheet2. Both tables must have the same header names and in the same order.

VBA code

Private Sub Worksheet_Activate()
Dim Value As Variant
Dim c As Integer
Dim Arr As Variant
ReDim Arr(0)
With Worksheets("Sheet1").ListObjects("Table1").AutoFilter
If .FilterMode Then
    For c = 1 To .Filters.Count
        If .Filters(c).On Then
            If IsArray(.Filters(c).Criteria1) Then
                For Each Value In .Filters(c).Criteria1
                    Arr(UBound(Arr)) = Mid(Value, 2, Len(Value))
                    ReDim Preserve Arr(UBound(Arr) + 1)
                ReDim Preserve Arr(UBound(Arr) - 1)
                Arr(UBound(Arr)) = .Filters(c).Criteria1
                ReDim Preserve Arr(UBound(Arr) + 1)
                On Error Resume Next
                Arr(UBound(Arr)) = .Filters(c).Criteria2
                If Err <> 0 Then ReDim Preserve Arr(UBound(Arr) - 1)
                On Error GoTo 0
            End If
            Worksheets("Sheet2").ListObjects("Table2").Range.AutoFilter Field:=c
            Worksheets("Sheet2").ListObjects("Table2").Range.AutoFilter Field:=c, Criteria1:=Arr, Operator:=xlFilterValues
        End If
For c = 1 To ActiveSheet.ListObjects("Table2").Range.Columns.Count
    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=c
End If
End With
End Sub
  1. Right click on sheet2
  2. Click "View Code"
  3. Paste vba code to sheet module
  4. Return to excel

Download excel *.xlsm file

Copy table filters.xlsm