I will in this article demonstrate a macro that copies criteria from one Excel Table and applies them to another Excel Table.

How it works

The animated image above shows selecting criteria manually in one Excel Table. An event macro then automatically copies the criteria to another table located on a different worksheet when that worksheet is activated.

It is required that both tables have the same column header names and the columns are arranged in the same order.

VBA code

'Event code located at a worksheet module
Private Sub Worksheet_Activate()

'Dimension variable and declare data types
Dim Value As Variant
Dim c As Integer
Dim Arr As Variant

'Redimension array variable Arr in order to programmatically increase array size
ReDim Arr(0)

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With Worksheets("Sheet1").ListObjects("Table1").AutoFilter

'Check if FilterMode is True
If .FilterMode Then

    'Iterate through filters
    For c = 1 To .Filters.Count

        'Check if filter is on
        If .Filters(c).On Then

            'Check if filter contains multiple conditions
            If IsArray(.Filters(c).Criteria1) Then

                'Iterate through each criteria value
                For Each Value In .Filters(c).Criteria1

                    'Save criteria value to array variable Arr
                    Arr(UBound(Arr)) = Mid(Value, 2, Len(Value))

                    'Increase array size with 1
                    ReDim Preserve Arr(UBound(Arr) + 1)

                'Continue with next criteria value
                Next

                'Decrease array size with 1
                ReDim Preserve Arr(UBound(Arr) - 1)

            'Excel continues here if filter contains a single condition
            Else
                
                'Save criteria value to array variable Arr
                Arr(UBound(Arr)) = .Filters(c).Criteria1

                'Increase size of array variable Arr
                ReDim Preserve Arr(UBound(Arr) + 1)

                'Enable error handling
                On Error Resume Next

                'Save criteria value to array variable Arr
                Arr(UBound(Arr)) = .Filters(c).Criteria2

                'Check if an error has occured then decrease array size with 1
                If Err <> 0 Then ReDim Preserve Arr(UBound(Arr) - 1)

                'Disable error handling
                On Error GoTo 0

            'Exit If statement
            End If

            'Enable Excel Table filter for Table2
            Worksheets("Sheet2").ListObjects("Table2").Range.AutoFilter Field:=c

            'Apply Excel Table filter criteria for Table2 based on variable c
            Worksheets("Sheet2").ListObjects("Table2").Range.AutoFilter Field:=c, Criteria1:=Arr, Operator:=xlFilterValues
        End If
    Next
Else

'Iterate 
For c = 1 To ActiveSheet.ListObjects("Table2").Range.Columns.Count
    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=c
Next
End If
End With
End Sub

Where to put the code?

1. Copy above VBA code.
2. Press with right mouse button on on tab sheet2, you will find it at the very bottom of the Excel screen.
   
3. Press with left mouse button on "View Code".
4. Paste VBA code to sheet module.
   
5. Return to excel.

Get the Excel file

Copy-table-filters.xlsm

---

Weekly Blog EMAIL

[newsletter_signup_form id=1]

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---

How to highlight row of the selected cell programmatically

Today I would like to share with you these small event handler procedures that make it easier for you to […]

Reorganize data [UDF]

Sean asks: Sheet1A B C D 8 Country Europe 9 Lights 100 10 Type A 200 11 12 Country USA […]

Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

Search two related tables [VBA]

This article demonstrates a macro that automatically applies a filter to an Excel defined Table based on the result from […]

Prepare data for Pivot Table – How to split concatenated values?

This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]

Excel calendar [VBA]

This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]