Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Dec 2002
    Location
    San Francisco, California, USA
    Posts
    54
    Thanks
    6
    Thanked 1 Time in 1 Post
    I've used auto filter for filtering my Excel 2003 data set (3200 rows, 11 columns). I have 75 different parties as a result of filtering Column A. Now, I want to print all 75 party's data sets. Is there an approach to print all 75 filtered party's data sets without printing them one at a time? If not, is there a filter collection or something similar in VBA? If I need to use VBA, any assistance in creating a macro would be very much appreciated. Thank you in advance.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Which column are the parties listed in? There is no programmatic access to the filter list dropdown, so you'll have to iterate the visible cells in that column, add them to a Collection or dictionary to get unique data, then filter for each in turn and print, but we'd need a bit more detail to write that.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Without seeing a sample of the Data, I am guessing this can be done with a Pivot Table. In the Layout for the field, choose "Insert page break after each item".

    Another alternative, try Data>Subtotals>Page break between groups

  4. #4
    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
    You may be able to adapt this code. It creates temp new sheet with a unique list based on column A, then goes through the list filtering the sheet and printing.

    Steve

    Code:
    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

  5. #5
    Star Lounger
    Join Date
    Dec 2002
    Location
    San Francisco, California, USA
    Posts
    54
    Thanks
    6
    Thanked 1 Time in 1 Post
    Thank you very much for all of the replies. sdckapr's VBA code solves the problem. The code prints out each filtered data set based on Column A's data. Now, I wish I understood what the VBA program was actually doing. Since you've solved my problem, I"m hesitant to request an explanation.

    Again, thank you very much.

    Regards,
    Larry

  6. #6
    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
    Don't know how much detail you need. Here is a brief overview. Ask if you need more details.

    [I suggest you comment out the line "Application.Screenupdating = false", then step through the code and watch what each step does. It can aid in the learning process...]

    Code defines the variables with DIMs
    Turns on error handling
    Turns off screenupdating to prevent flickering and speed code
    Defines variable iCol= 1 to column of interest (A)
    Sets the var wks to the active sheet (the sheet to filter)

    Checks if Autofilter is on, if not on, turns it on
    checks if data is filtered, if filtered, shows all the data

    Next section uses a Pivot table to create unique list of the items in the column of interest. This is created on a new sheet.
    On this sheet defines a range (set rPT) from row3 to the end of the data.
    Loops through this range (for each rCell) and filters on the value from the list, then prints it out

    After the loop is complete, it displays all the data, deletes the sheet with the pivot table, resets screenupdating, clears variables and exits...

    Steve


  7. #7
    Star Lounger
    Join Date
    Dec 2002
    Location
    San Francisco, California, USA
    Posts
    54
    Thanks
    6
    Thanked 1 Time in 1 Post
    Steve,

    Excellent. I followed your suggestion to comment out ScreenUpdating = False and then stepped through the program. Thank you, it was very helpful.

    Regards,

    Larry

Posting Permissions

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