Need Macro to print a sheet conatining drop down menu
 

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

Need Macro to print a sheet conatining drop down menu
 

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).

--
Regards,
Tom Ogilvy



"Faizal" wrote in message
om...
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