Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for Printing All Filtered Rows Separate
I have for example 500 rows of information with column A having item
numbers 123-11-001, 123-11-002, etc... I have the autofilter on so I can pick item 123-11-001 which has 10 rows of information and 123-11-002 that has about 20 rows of information. I have about 20 items and I have to pick each item individually to print them. Does anyone have a code so that it filters each item and prints each sheet filtered? Any help would be greatly appreciated. Darrell |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for Printing All Filtered Rows Separate
See Ron de Bruin's site:
http://www.rondebruin.nl/copy5.htm -- Regards, Tom Ogilvy "Darrell Lankford" wrote in message oups.com... I have for example 500 rows of information with column A having item numbers 123-11-001, 123-11-002, etc... I have the autofilter on so I can pick item 123-11-001 which has 10 rows of information and 123-11-002 that has about 20 rows of information. I have about 20 items and I have to pick each item individually to print them. Does anyone have a code so that it filters each item and prints each sheet filtered? Any help would be greatly appreciated. Darrell |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for Printing All Filtered Rows Separate
Darrell
I have some code that I believe does what you want, but, it first loads the uniqe records from the applicable database column into a listbox on a userform. From there, you can select all and print the database filtered for each unique entry, or you can filter and print only selected items. There are three command buttons on the userform. The userform initialization code, and the code for each button is shown below. I believe you will be most interested in the code for commandbutton1. Good luck. Ken Norfolk, Va Option Explicit Private Sub CommandButton1_Click() Dim i As Integer Dim item As Double For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = True Then item = ListBox1.List(i) Range("database_with_header").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:=item ActiveSheet.PrintOut End If Next i Selection.AutoFilter Unload Me End Sub Private Sub CommandButton2_Click() Unload Me End Sub Private Sub CommandButton3_Click() Dim i As Integer For i = 0 To ListBox1.ListCount - 1 ListBox1.Selected(i) = True Next i End Sub Private Sub UserForm_Initialize() Dim x() As Variant Dim R As Range Dim Count As Integer Dim i As Integer Dim j As Integer Set R = Range("database") Count = 0 'get list of items For i = 1 To R.Rows.Count For j = 1 To Count If R.Cells(i, 1) = x(j) Then GoTo 100 Next j Count = Count + 1 ReDim Preserve x(Count) x(Count) = R.Cells(i, 1) 100 Next i For i = LBound(x) + 1 To UBound(x) ListBox1.AddItem x(i) Next i End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for Printing All Filtered Rows Separate
Thanks for the assistance.
Darrell |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PRINTING IN FILTERED AREA | Excel Discussion (Misc queries) | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
Filtered results and printing | New Users to Excel | |||
Input filtered item in a separate cell | Excel Discussion (Misc queries) | |||
Filtered Visible Rows & VBA Non-Filtered Rows Displayed | Excel Programming |