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?

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

Next

--
Regards,
Tom Ogilvy



"Magoo" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 wrote:
> For Each itm In noDupes
> Selection.AutoFilter Field:=5, Criteria1:=itm
> Selection.AutoFilter Field:=6, Criteria1:=sMonth
> ActiveSheet.AutoFilter.Range.PrintPreview
>
> Next
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Magoo" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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?
> >Hello Tom you have come to my rescue again. Thanks I will try this right away.

Magoo wrote:
> Tom Ogilvy wrote:
> > For Each itm In noDupes
> > Selection.AutoFilter Field:=5, Criteria1:=itm
> > Selection.AutoFilter Field:=6, Criteria1:=sMonth
> > ActiveSheet.AutoFilter.Range.PrintPreview
> >
> > Next
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Magoo" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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?
> > >Hello Tom you have come to my rescue again. Thanks I will try this right away.

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?

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.
--
Regards,
Tom Ogilvy
..


"Magoo" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Magoo wrote:
>> Tom Ogilvy wrote:
>> > For Each itm In noDupes
>> > Selection.AutoFilter Field:=5, Criteria1:=itm
>> > Selection.AutoFilter Field:=6, Criteria1:=sMonth
>> > ActiveSheet.AutoFilter.Range.PrintPreview
>> >
>> > Next
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> >
>> > "Magoo" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> > > 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?
>> > >Hello Tom you have come to my rescue again. Thanks I will try this
>> > >right away.
>
> 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 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.

Tom Ogilvy wrote:
> 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.
> --
> Regards,
> Tom Ogilvy
> .
>
>
> "Magoo" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >
> > Magoo wrote:
> >> Tom Ogilvy wrote:
> >> > For Each itm In noDupes
> >> > Selection.AutoFilter Field:=5, Criteria1:=itm
> >> > Selection.AutoFilter Field:=6, Criteria1:=sMonth
> >> > ActiveSheet.AutoFilter.Range.PrintPreview
> >> >
> >> > Next
> >> >
> >> > --
> >> > Regards,
> >> > Tom Ogilvy
> >> >
> >> >
> >> >
> >> > "Magoo" <(E-Mail Removed)> wrote in message
> >> > news:(E-Mail Removed)...
> >> > > 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?
> >> > >Hello Tom you have come to my rescue again. Thanks I will try this
> >> > >right away.
> >
> > 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?
> >

If you want to manually apply the filter and choose the month, then you
almost had it:

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


--
regards,
Tom Ogilvy


"Magoo" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> Tom Ogilvy wrote:
>> 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.
>> --
>> Regards,
>> Tom Ogilvy
>> .
>>
>>
>> "Magoo" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> >
>> > Magoo wrote:
>> >> Tom Ogilvy wrote:
>> >> > For Each itm In noDupes
>> >> > Selection.AutoFilter Field:=5, Criteria1:=itm
>> >> > Selection.AutoFilter Field:=6, Criteria1:=sMonth
>> >> > ActiveSheet.AutoFilter.Range.PrintPreview
>> >> >
>> >> > Next
>> >> >
>> >> > --
>> >> > Regards,
>> >> > Tom Ogilvy
>> >> >
>> >> >
>> >> >
>> >> > "Magoo" <(E-Mail Removed)> wrote in message
>> >> > news:(E-Mail Removed)...
>> >> > > 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?
>> >> > >Hello Tom you have come to my rescue again. Thanks I will try this
>> >> > >right away.
>> >
>> > 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?
>> >
>

Thank you very much Tom. You have saved me a lot of time.
Tom Ogilvy wrote:
> If you want to manually apply the filter and choose the month, then you
> almost had it:
>
> Sub abc()
> Dim noDupes As New Collection
> Dim rw As Long
> Dim itm As Variant
> rw = ActiveSheet.AutoFilter.Range.Row
> For Each cell In ActiveSheet.AutoFilter.Range.Columns(5).Cells
> If cell.Row <> rw Then
> If Not cell.EntireRow.Hidden Then
> On Error Resume Next
> noDupes.Add cell.Value, cell.Text
> On Error GoTo 0
> End If
> End If
> Next
> For Each itm In noDupes
> Selection.AutoFilter Field:=5, Criteria1:=itm
> ActiveSheet.AutoFilter.Range.PrintPreview
> Next
> End Sub
>
>
> --
> regards,
> Tom Ogilvy
>
>
> "Magoo" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
> >
> > Tom Ogilvy wrote:
> >> 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.
> >> --
> >> Regards,
> >> Tom Ogilvy
> >> .
> >>
> >>
> >> "Magoo" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> >
> >> > Magoo wrote:
> >> >> Tom Ogilvy wrote:
> >> >> > For Each itm In noDupes
> >> >> > Selection.AutoFilter Field:=5, Criteria1:=itm
> >> >> > Selection.AutoFilter Field:=6, Criteria1:=sMonth
> >> >> > ActiveSheet.AutoFilter.Range.PrintPreview
> >> >> >
> >> >> > Next
> >> >> >
> >> >> > --
> >> >> > Regards,
> >> >> > Tom Ogilvy
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Magoo" <(E-Mail Removed)> wrote in message
> >> >> > news:(E-Mail Removed)...
> >> >> > > 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?
> >> >> > >Hello Tom you have come to my rescue again. Thanks I will try this
> >> >> > >right away.
> >> >
> >> > 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 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.
Magoo wrote:
> Thank you very much Tom. You have saved me a lot of time.
> Tom Ogilvy wrote:
> > If you want to manually apply the filter and choose the month, then you
> > almost had it:
> >
> > Sub abc()
> > Dim noDupes As New Collection
> > Dim rw As Long
> > Dim itm As Variant
> > rw = ActiveSheet.AutoFilter.Range.Row
> > For Each cell In ActiveSheet.AutoFilter.Range.Columns(5).Cells
> > If cell.Row <> rw Then
> > If Not cell.EntireRow.Hidden Then
> > On Error Resume Next
> > noDupes.Add cell.Value, cell.Text
> > On Error GoTo 0
> > End If
> > End If
> > Next
> > For Each itm In noDupes
> > Selection.AutoFilter Field:=5, Criteria1:=itm
> > ActiveSheet.AutoFilter.Range.PrintPreview
> > Next
> > End Sub
> >
> >
> > --
> > regards,
> > Tom Ogilvy
> >
> >
> > "Magoo" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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.
> > >
> > > Tom Ogilvy wrote:
> > >> 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.
> > >> --
> > >> Regards,
> > >> Tom Ogilvy
> > >> .
> > >>
> > >>
> > >> "Magoo" <(E-Mail Removed)> wrote in message
> > >> news:(E-Mail Removed)...
> > >> >
> > >> > Magoo wrote:
> > >> >> Tom Ogilvy wrote:
> > >> >> > For Each itm In noDupes
> > >> >> > Selection.AutoFilter Field:=5, Criteria1:=itm
> > >> >> > Selection.AutoFilter Field:=6, Criteria1:=sMonth
> > >> >> > ActiveSheet.AutoFilter.Range.PrintPreview
> > >> >> >
> > >> >> > Next
> > >> >> >
> > >> >> > --
> > >> >> > Regards,
> > >> >> > Tom Ogilvy
> > >> >> >
> > >> >> >
> > >> >> >
> > >> >> > "Magoo" <(E-Mail Removed)> wrote in message
> > >> >> > news:(E-Mail Removed)...
> > >> >> > > 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?
> > >> >> > >Hello Tom you have come to my rescue again. Thanks I will try this
> > >> >> > >right away.
> > >> >
> > >> > 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?
> > >> >
> > >