Results 1 to 3 of 3

Thread: Autofilter loop through a list of criteria

  1. #1
    Seeker Zippymouse's Avatar
    Join Date
    Jul 2019
    Posts
    5
    Articles
    0
    Excel Version
    2019

    Autofilter loop through a list of criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hey there,

    I am trying to create a macro that will:
    1. Filter by the first item in a list of criteria
    2. Save a PDF of the result
    3. Repeat steps 1-2 until it has worked through the list of criteria

    Below is my current formula which works, but only filters by the value in cell C1. I'd like to replace C1 with a range of cells that contain a list of criteria, and have the macro work through that list 1 by 1 creating a PDF for each individual result until it has worked through the list.

    Any thoughts?

    Thanks!



    Code:
    Sub Filter()
    
    
    ' Filter by vlue
    ActiveSheet.Range("$A$24:$D$5000").AutoFilter Field:=10, Criteria1:= _
        Range("c2"), Operator:=xlAnd
    
    
    'save as pdf
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        ThisWorkbook.Path & "" & "EMF " & Range("c2") & " for " & Range("b7"), _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True
    
    
    End Sub
    Last edited by AliGW; 2019-07-28 at 08:04 AM. Reason: Code tags added - remember next time, please.

  2. #2
    Neophyte ges's Avatar
    Join Date
    Jul 2019
    Location
    Italy
    Posts
    3
    Articles
    0
    Excel Version
    2016
    Hi,
    let's set range of criteria both in C1:C10
    Code:
    Sub Filter()
    
    Dim i As Long
    Dim myCriteria As String
    
    
        For i = 1 To 10
        
           'Criteria
            myCriteria = Cells(i, "C")
            
            ' Filter by vlue
            ActiveSheet.Range("A24").AutoFilter Field:=10, Criteria1:=myCriteria
    
    
            'save as pdf
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                ThisWorkbook.Path & "" & "EMF " & myCriteria & " for " & Range("b7")
    
    
        Next i
        
    End Sub
    let's set the range of criteria both in C1: C10

  3. #3
    Seeker Zippymouse's Avatar
    Join Date
    Jul 2019
    Posts
    5
    Articles
    0
    Excel Version
    2019
    That works perfectly, thanks so much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •