Autofilter Printing

[Magoo]

Using the following code, I am able to automatically print (title5
Column) 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.
________________________________________________________________________

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
________________________________________________________________________


I would now like to add a function to the macro that would allow me to
print those same results based on the month that I choose.


Example Below:
Name Title 2 Title 3 Title 4 Title 5 Month
Data 1 101 10 101 Unique Data1 February
Data 2 101 10 101 Unique Data1 February
Data 3 101 10 101 Unique Data2 February
Data 4 101 10 101 Unique Data3 March
Data 5 101 10 101 Unique Data4 March
Data 6 101 10 101 Unique Data5 April
Data 7 101 10 101 Unique Data1 May


Is there a way to do this?

 

[Tom Ogilvy]

For Each itm In noDupes
Selection.AutoFilter Field:=5, Criteria1:=itm
Selection.AutoFilter Field:=6, Criteria1:=sMonth
ActiveSheet.AutoFilter.Range.PrintPreview

Next

 

[Magoo]

For Each itm In noDupes
Selection.AutoFilter Field:=5, Criteria1:=itm
Selection.AutoFilter Field:=6, Criteria1:=sMonth
ActiveSheet.AutoFilter.Range.PrintPreview

Next

 

[Magoo]

Hello Tom. I know that I did something wrong or omitted something. Here
is what I have.

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
Selection.AutoFilter Field:=6, Criteria1:=sMonth
ActiveSheet.AutoFilter.Range.PrintPreview
Next

should I Dim sMonth?

 

[Tom Ogilvy]

You said based on the month that you choose.

So that choice would be held in sMonth and yes, sMonth should be declared
and an appropriate value assigned to it.

I was just illustrating that you would apply this criteria to the
appropriate column of the autofilter.


Dim sMonth as String

sMonth = "February"
For Each itm In noDupes
Selection.AutoFilter Field:=5, Criteria1:=itm
Selection.AutoFilter Field:=6, Criteria1:=sMonth
ActiveSheet.AutoFilter.Range.PrintPreview
Next

is just illustrative. You would have to adapt it to your situation.

 

[Magoo]

Tom would this allow me the ability of choosing a month using the
autofilter, and cycle through everything that took place in that month
only? Or would I have to go in and edit the "month" each time I print a
monthly report? What I would like to do is use an autofilter to select
a month and then cycle through item for that month. Maybe I could write
another macro that automatically places the correct month into this
Macro. Would that work?

Please bear with me. I greatly appreciate you helping me with this.

 

[Magoo]

Thank you very much Tom. You have saved me a lot of time.

 

[Magoo]

Tom I have one final question in regards to this same spreadsheet. Is
it possible to pass the autofilter results of one column to the
worksheet name. In other words Can I filter all october results and
because the filter is only displaying October results it changes the
name of the worksheet Tab to October? If this is not possible. Is it
possible to add an input prompt to the macro that asks for the month
before the macro is carried out. Before the last change each of my
worksheet tabs were named by Months January, February etc. Now all
months are going to be on the same worksheet. I would like to be able
to change the name of the worksheet automatically and then back to a
generic name if possible.