Need Macro to print a sheet conatining drop down menu

[Faizal]

Hi,

I got a worksheet with drop down menu containg 10 items in the drop
down and other information connected to database. i want to print each
item from the drop down menu. so i don't want to choose each item
every time and then print. so in normal case i have to select each
item and then click on print.so i have to do this 10 times. all i want
to do is with one click it should select automatically from the drop
down menu and print. but i have tried recording the macro but is not
working.it is selecting active sheet and what ever the item is active
in the drop down it prints q0 times. so i need someone who can write a
macro that can choose the second item, third item and so on and print
them indivdually.

any info related to the query is highly appreciated.

Thanks in advance.

Faizal Cassim

 

[Tom Ogilvy]

Sub PrintAutoFilterItems()
Dim nodupes As New Collection
Dim rng As Range
Dim Cell As Range
Dim item As Variant
Set rng = ActiveSheet.AutoFilter.Range.Columns(1).Cells
Debug.Print rng.Count, rng.Address
On Error Resume Next
For Each Cell In rng
If Cell.Row <> rng(1).Row Then
nodupes.Add Cell.Value, Cell.Text
End If
Next

For Each item In nodupes
ActiveSheet.AutoFilter.Range.AutoFilter _
Field:=1, Criteria1:=item
ActiveSheet.PrintPreview
Next item

End Sub

Assumes you are talking about an autofilter and not a pivot table and that
you are filtering on the first column in the autofilter. (Easily altered
for filtering in other columns).