如果要在传输数据之前使用过滤器，则需要事先将其应用于源原点。
你可以用Autofilter这样做：

'Filter the data to use only supplied Name
    Dim FilterColumn As Long
    FilterColumn = Application.Match(FilterName, SourceTable.HeaderRowRange, 0)
    SourceTable.DataBodyRange.AutoFilter Field:=FilterColumn, Criteria1:=Criteria

过滤器所做的基本上是隐藏不匹配条件的行（零高度），因此当您传输数据时，您需要确保仅使用可见的行（例如.SpecialCells(xlCellTypeVisible)）。
将所有这些放在一起将给予：

Sub Test()

    'Define your main tables
    Dim SourceTable As ListObject
    Set SourceTable = ThisWorkbook.Worksheets("Sheet2").ListObjects("Table2")
    
    Dim DestTable As ListObject
    Set DestTable = ThisWorkbook.Worksheets("Sheet3").ListObjects("Table3")
    
    'Define the filter values
    Dim RefTable As ListObject
    Set RefTable = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")
    
    Dim FilterName As String
    FilterName = "Name"
    
    'Define filter
    Dim NameValue As String, col As Long
    col = Application.Match("Name", RefTable.HeaderRowRange, 0)
    NameValue = RefTable.DataBodyRange.Cells(1, col)
    
    If NameValue = "" then
        NameValue = "Default"
    End If

    CopyFilteredTable FilterName, NameValue, SourceTable, DestTable

End Sub

Sub CopyFilteredTable(ByVal FilterName As Variant, ByVal Criteria As Variant, SourceTable As ListObject, DestTable As ListObject)
   
    'Filter the data to use only supplied criteria
    Dim FilterColumn As Long
    FilterColumn = Application.Match(FilterName, SourceTable.HeaderRowRange, 0)
    SourceTable.DataBodyRange.AutoFilter Field:=FilterColumn, Criteria1:=Criteria
    
    With DestTable
    
        'Clear destination table
        On Error Resume Next
            .DataBodyRange.Clear
            .Resize .Range.Resize(SourceTable.ListRows.SpecialCells(xlCellTypeVisible).Count + 1, .ListColumns.Count)
        On Error GoTo 0

        'Loop through target header and collect columns from Source Table
        Dim lc As Long
        For lc = 1 To .ListColumns.Count
            
            Dim mc As Variant
            mc = Application.Match(.HeaderRowRange(lc), SourceTable.HeaderRowRange, 0)
                        
            If Not IsError(mc) Then
            
                Dim ColRange As Range
                Set ColRange = SourceTable.ListColumns(mc).DataBodyRange.SpecialCells(xlCellTypeVisible)
                
                .ListColumns(lc).DataBodyRange.Resize(ColRange.Rows.Count, ColRange.Columns.Count).Value2 = ColRange.Value2
                
            End If
            
        Next lc

    End With

End Sub

之前

之后

请注意，这将使您的源表处于过滤模式。如果有问题，你可以在最后加上SourceTable.AutoFilter.ShowAllData。
编辑1：如果你想保留格式，你可以使用Copy方法，而不是只传输值，但请注意，这样会更慢。

ColRange.Copy Destination:=.ListColumns(lc).DataBodyRange.Resize(ColRange.Rows.Count, ColRange.Columns.Count)

EDIT2：要处理引用名称与源表中的任何名称都不匹配的情况，可以在过滤器后添加一个检查，如果过滤后的表中不存在数据，则使用“Default”过滤器重新运行过滤器。

On Error Resume Next
        Dim test As String
        test = SourceTable.DataBodyRange.SpecialCells(xlCellTypeVisible).Address
        If Err.Number = 1004 Then 'No cells were found.
            SourceTable.DataBodyRange.AutoFilter Field:=FilterColumn, Criteria1:="Default"
        Else
            Err.Raise Err.Number, Err.Source, Err.Description
        End If
    On Error GoTo 0