Autofilter using multiple references in another file

[jab973]

Hi there! I have a file that I am autofiltering by year and copying into another file. I have tried a few approachs below. It works if I just filter by one year and it works if I hard code the 3 years I want in the macro, but when I try to use array with the links, no information gets pulled in (but there is no error message). Is this possible? Any help would be appreciated!

'Dim arr As Variant
'arr = Array(ThisWorkbook.Worksheets("Linking Criteria").Range("L10").Value, _
ThisWorkbook.Worksheets("Linking Criteria").Range("L11").Value, _
ThisWorkbook.Worksheets("Linking Criteria").Range("L12").Value)

With ws.Range("A1", ws.Cells(lr, lc))
'.AutoFilter Field:=6, Criteria1:=ThisWorkbook.Worksheets("Linking Criteria").Range("L10").Value ' works with single year link
.AutoFilter Field:=6, Criteria1:=Array("2020", "2021", "2022"), Operator:=xlFilterValues 'array works when hard coded
'.AutoFilter Field:=6, Criteria1:=Array(arr), Operator:=xlFilterValues 'array doesn't work with links
.SpecialCells(xlCellTypeVisible).Copy
End With
ThisWorkbook.Activate
Worksheets("Disb Import").Range("a5").PasteSpecial xlPasteValues

 

[kevin9999]

Try the following. Assumes your "ws" (above) is Sheet1.

Option Explicit
Sub Filter_Years()
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim c As Range, i As Long, arr
    
    Set ws1 = Sheet1
    Set ws2 = Worksheets("Linking Criteria")
    Set ws3 = Worksheets("Disb Import")
    
    arr = ws2.Range("L10:L12").Value
    arr = Application.Transpose(Application.Index(arr, 0, 1))
    For i = LBound(arr) To UBound(arr)
        arr(i) = CStr(arr(i))
    Next i
    
    With ws1.Range("A1").CurrentRegion
        .AutoFilter 6, Array(arr), 7
        .Offset(1).Copy ws3.Range("A5")
        .AutoFilter
    End With
End Sub

 

[jab973]

Try the following. Assumes your "ws" (above) is Sheet1.

Option Explicit
Sub Filter_Years()
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim c As Range, i As Long, arr
   
    Set ws1 = Sheet1
    Set ws2 = Worksheets("Linking Criteria")
    Set ws3 = Worksheets("Disb Import")
   
    arr = ws2.Range("L10:L12").Value
    arr = Application.Transpose(Application.Index(arr, 0, 1))
    For i = LBound(arr) To UBound(arr)
        arr(i) = CStr(arr(i))
    Next i
   
    With ws1.Range("A1").CurrentRegion
        .AutoFilter 6, Array(arr), 7
        .Offset(1).Copy ws3.Range("A5")
        .AutoFilter
    End With
End Sub

Thank you...that works exactly like I wanted!!

 

[kevin9999]

Thank you...that works exactly like I wanted!!

Glad I could help and thanks for the feedback ?