Snasui Excel Forum

Code: Select all

Sub Advancefilter ()
  For r = 2 To Range("Z65536").End(xlUp).Row
  Range("AA2").Value = Range("z" & r).Value
  xData = Range("AA2").Value
  Range("A1:Y5000").Select
  Selection.Advancefilter Action :=xlfilterInplace, CriteriaRange:=Range("AA1:AA2"), Unique:=False
  Activesheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
Activesheet.showdata
  Next
End sub

Code: Select all

Sub AdvanceFilter()
    Dim criRng As Range
    With ActiveSheet
        Set criRng = .Range("z1", .Range("z" & .Rows.Count).End(xlUp))
        .Range("A1").CurrentRegion.Select
        Selection.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=criRng, Unique:=False
            ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
        If .AutoFilterMode Then
            .ShowAllData
        End If
    End With
End Sub