Seeking VBA method to filter multiple values

[auto.pilot]

I have the following bit of code which is part of a larger project. Near the top, I have identified TheClient as a single cell reference. Within the code, a workbook is opened, then filtered by TheClient in column A. Thereafter, the headers and filtered range are copied and then pasted to the destination workbook. This all works as expected.

However, I would like to now change the code so that TheClient is many Clients, up to 300. How can I do this?

Dim DestBook As Workbook
Dim TheClient As Long
Dim Last_A As Long

Set DestBook = ActiveWorkbook

TheClient = Range("A1").Value  ' <<< instead of one client, I would like to make this many clients (up to 300).

Set SourceBook = Workbooks.Open(Filename:="\\blablabla.SourceBook.xlsx")

Last_A = Range("A" & Rows.Count).End(xlUp).Row

ActiveSheet.Range("A1:IL" & Last_A).AutoFilter Field:=1, Criteria1:=TheClient   ''' <<< same here, I'd like to filter the list for many Clients, not just one 
Range("A1:IL" & Last_A).Copy

DestBook.Activate

Set PasteDest = Range("A6")

PasteDest.Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

SourceBook.Close False

Thanks in advance for all help.

j

 

[John_w]

Try this macro. Clients in A1, A2, A3, etc. on the active sheet.

Public Sub AutoFilter_Other_Workbook_Multiple_Values1()

    Dim DestBook As Workbook
    Dim SourceBook As Workbook
    Dim Last_A As Long
    Dim ColAValues As Variant, Clients() As String, i As Long
    
    Set DestBook = ActiveWorkbook
    
    With DestBook.ActiveSheet
        Last_A = .Range("A" & .Rows.Count).End(xlUp).Row
        ColAValues = .Range("A1:A" & Last_A).Value
    End With
    ReDim Clients(1 To UBound(ColAValues))
    For i = 1 To UBound(ColAValues)
        Clients(i) = ColAValues(i, 1)
    Next
    
    Set SourceBook = Workbooks.Open(Filename:="C:\folder\path\SourceBook.xlsx")
    With SourceBook.ActiveSheet
        Last_A = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A1:IL" & Last_A).AutoFilter Field:=1, Criteria1:=Clients, Operator:=xlFilterValues
        .Range("A1:IL" & Last_A).Copy
    End With
        
    DestBook.Activate
    DestBook.ActiveSheet.Range("C1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    SourceBook.Close False

End Sub

 

[auto.pilot]

Thank you, I will give it a go this morning.

j

 

[auto.pilot]

It only took minutes for me to try, and it works perfectly. Thanks for your prompt and excellent reply.

j