| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter Printing
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Printing
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" wrote in message oups.com... 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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Printing
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" wrote in message oups.com... 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Printing
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" wrote in message oups.com... 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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Printing
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" wrote in message ups.com... 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" wrote in message oups.com... 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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Printing
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" wrote in message ups.com... 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" wrote in message oups.com... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
| 2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
| Printing from Userform with AutoFilter | Excel Discussion (Misc queries) | |||
| Printing Headers using Criteria chosen from AutoFilter | Excel Programming | |||
| Printing slected items after using autofilter | Excel Discussion (Misc queries) | |||
Hybrid Mode
