Automatically Printing Autofilters

[Magoo]

Hello Everyone,
I am very new to VBA programming and was wondering if automatic
autofilter printing was possible via Excel vba macro?


What I would like to do is to automatically print autofilter results
based on the autofilters of a designated column, so that I don't have
to select each autofilter one at a time and print. This would be a
great time saver.
See Example Below.
Using the Autofilter on the Title 5 column I would like to
automatically cycle through the various filters and print them.


Title 1 Title 2 Title 3 Title 4 Title 5
Data 1 101 101 101 Unique Data1
Data 2 102 102 102 Unique Data1
Data 3 103 103 103 Unique Data1
Data 4 104 104 104 Unique Data1
Data 5 105 105 105 Unique Data2
Data 6 106 106 106 Unique Data3
Data 7 107 107 107 Unique Data4
Data 8 108 108 108 Unique Data5
Data 9 109 109 109 Unique Data1


Here is what I was able to record:


Sub test2()
'


Selection.AutoFilter Field:=5, Criteria1:="Unique Data1"
ActiveWindow.SelectedSheets.PrintPreview
End Sub


The problem with this is that I cannot figure out a continuous loop of
some sort.
Any help would be greatly appreciated?

 

[Tom Ogilvy]

Sub abc()
Dim noDupes As New Collection
Dim rw As Long
Dim itm As Variant
Selection.AutoFilter Field:=5
rw = ActiveSheet.AutoFilter.Range.Row
For Each cell In ActiveSheet.AutoFilter.Range.Columns(5).Cells
If cell.Row <> rw Then
On Error Resume Next
noDupes.Add cell.Value, cell.Text
On Error GoTo 0
End If
Next
For Each itm In noDupes
Selection.AutoFilter Field:=5, Criteria1:=itm
ActiveSheet.AutoFilter.Range.PrintPreview
Next

End Sub

Change PrintPreview to PrintOut

 

[Magoo]

Thanks Tom, I will attempt your suggestion.

 

[Magoo]

It works! Is there any way to have it to print the active window
instead of the whole active sheet.

 

[Tom Ogilvy]

It should be printing just the filtered data. I am not sure what you mean
by active window.

You can change it to

ActiveWindow.VisibleRange.Printout

but that doesn't seem desirable to me.

 

[Magoo]

Well Tom, sometimes my spreadsheets look a lot like the following, were
I am only concerned about printing the middle columns. I usually use
set print areas to accomplish this task. I also give the page setup
manager rows to repeat at the top.

Less important Data 101 101 101 Unique Data1 Less important Data
Less important Data 102 102 102 Unique Data1 Less important Data
Less important Data 103 103 103 Unique Data1 Less important Data
Less important Data 104 104 104 Unique Data2 Less important Data
Less important Data 105 105 105 Unique Data3 Less important Data
Less important Data 106 106 106 Unique Data4 Less important Data
Less important Data 107 107 107 Unique Data5 Less important Data
Less important Data 108 108 108 Unique Data3 Less important Data
Less important Data 109 109 109 Unique Data3 Less important Data

Is there any way to do this?

 

[Tom Ogilvy]

Set your print area and rows to repeat

then change the command to

Activesheet.Printout

in you printarea (only needs to be set once)
use an area for the entire database minus the columns you are not interested
in.

Hidden rows won't print.

 

[Magoo]

Tom that worked! You are great at this.
Thanks alot.