Autofilter loop through a list of criteria

Z

Zippymouse

New member
Joined
Jul 28, 2019
Messages
6
Reaction score
0
Points
1
Excel Version(s)
2019
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 a moderator:
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
 
That works perfectly, thanks so much!
 
You must log in or register to reply here.
Back
Top