Results 1 to 5 of 5
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Printout of each item from an Autofilter list (All)

    On occasion I have been asked how to loop thru the items in an autofilter list with the goal being to select each unique item in the column and printout the filtered lists. To do this, I have written the attached code which could be adapted as needed. The Variable "iCol" holds the column number you want to filter (change as desired).

    The code creates a temporary pivot table to hold the unique list of items and loops thru this list changing the autofilter during each loop and then printing it.

    I hope some of the viewers here find it useful.

    Steve

    <pre>Option Explicit
    Sub PrintAllAutofilter()
    Dim wks As Worksheet
    Dim PT As PivotTable
    Dim rPT As Range
    Dim rCell As Range
    Dim iCol As Integer
    Dim sHeader As String
    On Error GoTo Errhandler
    Application.ScreenUpdating = False
    iCol = 1 'Filter all on Col A

    Set wks = ActiveSheet
    With wks
    If Not .AutoFilterMode Then
    .Range("a1").AutoFilter
    End If
    If .FilterMode Then .ShowAllData

    Set PT = .PivotTableWizard _
    (SourceType:=xlDatabase, _
    SourceData:=.Range("a1").CurrentRegion, _
    TableDestination:="", _
    TableName:="PivotTable1")
    sHeader = .Cells(1, iCol)
    With PT
    .AddFields RowFields:=sHeader
    .PivotFields(sHeader).Orientation = xlDataField
    .ColumnGrand = False
    End With
    With ActiveSheet
    Set rPT = .Range(Range("A3"), _
    Range("A65536").End(xlUp))
    End With
    For Each rCell In rPT
    .Range("a1").AutoFilter Field:=iCol, _
    Criteria1:=rCell.Value
    .PrintOut
    Next
    .ShowAllData
    End With
    Application.DisplayAlerts = False
    ActiveSheet.Delete

    ExitHandler:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Set rCell = Nothing
    Set rPT = Nothing
    Set PT = Nothing
    Set wks = Nothing
    Exit Sub

    Errhandler:
    MsgBox Err.Number & ":" & Err.Description
    Resume ExitHandler
    End Sub</pre>


  2. #2
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printout of each item from an Autofilter list (All)

    Thank you Steve,

    very useful.

    In order to be "compatible" with the new coming Excel version I would use
    With ActiveSheet
    Set rPT = .Range(Range("A3"), _
    Range("A"&cells.rows.count).End(xlUp))
    End With

    Wolf

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Printout of each item from an Autofilter list (All)

    Hi Steve

    I have a use for this excellent macro, but is it possible to print two columns instead of one.

    For instance I would like to Produce Column A containing Code and Column B containing description.

    Many thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printout of each item from an Autofilter list (All)

    The macro will print the entire sheet. It'll generate a separate printout for each value in the specified column. So if you specify iCol = 1, it'll generate a printout for each value of Code, and it'll print all available columns.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Printout of each item from an Autofilter list (All)

    Hi Hans

    Thanks for the reply, i apologise for not testing it properly.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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