Filtering based on a range of names from a different sheet-CodePudding

I'm trying to filter on range of names, without adding them manually, not only one name like right now, from the Pos_data to T_Data

My code:


   Dim category As Range
    
   With Worksheets("Pos_Data")
    
        Set category = .Range("U2")
        
   End With
    
    With Worksheets("T_Data")
    
       With .Range("A1:CP1503")
        
         .AutoFilter Field:=14, Criteria1:=category, VisibleDropDown:=True
            
       End With
        
        
    End With
    
    
End Sub

Names appear in the column U of Pos_Data

Question is: how to have a filter that picks all the names from column U and then applies it in T_Data Column N

Like here, I've got names and without manually writing them in the code I would like to filter on them in a new tab column N

CodePudding user response：

You must construct an array of your names from the values in Column U. This example will hopefully give you a start.

Option Explicit

Sub FilterForNames()
    Dim categories As Range
    Dim categoryList As Variant
    
    '--- build an array of the names in the filter
    With Worksheets("Pos_Data")
        Set categories = .Range("U2:U5")
        ReDim categoryList(1 To categories.Cells.Count)
    
        Dim i As Long
        For i = 1 To UBound(categoryList)
            categoryList(i) = categories.Cells(i, 1).Value
        Next i
    End With
    
    With Worksheets("T_Data")
       With .Range("A1:CP1503")
         .AutoFilter Field:=14, Operator:=xlFilterValues, Criteria1:=categoryList
       End With
    End With
End Sub