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

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

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

Code for Printing All Filtered Rows Separate
 

Thanks for the assistance.
Darrell