HELP! - Printing a sheet with values populated from dropdown box

Hello,

I have a frustrating problem, I have a dropdown box in Excel which holds
around 1,000 numbers (cost centres). When I select a number the values on the
spreadsheet change to those respective to the selected cost centre (i.e.
sales £, volume).

What I would like is a macro to loop through every cost centre in the
dropdown box and print for all 1,000 selections in the dropdown. Is this
possible? Otherwise i'd have to go through them all one by one to print them
off.

Any help much appreciated. Regards Brian Taylor, Co-op Bank, Manchester, UK

Is the dropdown box from an autofilter? If so, there is no way to access
that list. You can build a list you can loop through by using the Advanced
filter with the unique option.

If it is from data validation, then just use the source data you used to
populate the list.

--
Regards,
Tom Ogilvy

"Co-op Bank" <CoopBank@discussions.microsoft.com> wrote in message
news:B0D99C29-FF7A-4056-807D-B6C84055B4EA@microsoft.com...
> Hello,
>
> I have a frustrating problem, I have a dropdown box in Excel which holds
> around 1,000 numbers (cost centres). When I select a number the values on
the
> spreadsheet change to those respective to the selected cost centre (i.e.
> sales £, volume).
>
> What I would like is a macro to loop through every cost centre in the
> dropdown box and print for all 1,000 selections in the dropdown. Is this
> possible? Otherwise i'd have to go through them all one by one to print
them
> off.
>
> Any help much appreciated. Regards Brian Taylor, Co-op Bank, Manchester,
UK

please think of the forests..
printing 1000 pages is a waste of paper.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Co-op Bank wrote :

> Hello,
>
> I have a frustrating problem, I have a dropdown box in Excel which
> holds around 1,000 numbers (cost centres). When I select a number the
> values on the spreadsheet change to those respective to the selected
> cost centre (i.e. sales £, volume).
>
> What I would like is a macro to loop through every cost centre in the
> dropdown box and print for all 1,000 selections in the dropdown. Is
> this possible? Otherwise i'd have to go through them all one by one
> to print them off.
>
> Any help much appreciated. Regards Brian Taylor, Co-op Bank,
> Manchester, UK

Sorry its from a validation drop down box. cheers

"Tom Ogilvy" wrote:

> Is the dropdown box from an autofilter? If so, there is no way to access
> that list. You can build a list you can loop through by using the Advanced
> filter with the unique option.
>
> If it is from data validation, then just use the source data you used to
> populate the list.
>
> --
> Regards,
> Tom Ogilvy
>
> "Co-op Bank" <CoopBank@discussions.microsoft.com> wrote in message
> news:B0D99C29-FF7A-4056-807D-B6C84055B4EA@microsoft.com...
> > Hello,
> >
> > I have a frustrating problem, I have a dropdown box in Excel which holds
> > around 1,000 numbers (cost centres). When I select a number the values on
> the
> > spreadsheet change to those respective to the selected cost centre (i.e.
> > sales £, volume).
> >
> > What I would like is a macro to loop through every cost centre in the
> > dropdown box and print for all 1,000 selections in the dropdown. Is this
> > possible? Otherwise i'd have to go through them all one by one to print
> them
> > off.
> >
> > Any help much appreciated. Regards Brian Taylor, Co-op Bank, Manchester,
> UK
>
>
>

So work from the list that you use as the source of the validation dropdown.

--
Regards,
Tom Ogilvy

"Co-op Bank" <CoopBank@discussions.microsoft.com> wrote in message
news:AACF1D79-FA44-4E54-81C8-DA02DD82B4B9@microsoft.com...
> Sorry its from a validation drop down box. cheers
>
> "Tom Ogilvy" wrote:
>
> > Is the dropdown box from an autofilter? If so, there is no way to
access
> > that list. You can build a list you can loop through by using the
Advanced
> > filter with the unique option.
> >
> > If it is from data validation, then just use the source data you used to
> > populate the list.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Co-op Bank" <CoopBank@discussions.microsoft.com> wrote in message
> > news:B0D99C29-FF7A-4056-807D-B6C84055B4EA@microsoft.com...
> > > Hello,
> > >
> > > I have a frustrating problem, I have a dropdown box in Excel which
holds
> > > around 1,000 numbers (cost centres). When I select a number the values
on
> > the
> > > spreadsheet change to those respective to the selected cost centre
(i.e.
> > > sales £, volume).
> > >
> > > What I would like is a macro to loop through every cost centre in the
> > > dropdown box and print for all 1,000 selections in the dropdown. Is
this
> > > possible? Otherwise i'd have to go through them all one by one to
print
> > them
> > > off.
> > >
> > > Any help much appreciated. Regards Brian Taylor, Co-op Bank,
Manchester,
> > UK
> >
> >
> >

then try:

Sub LoopTheValListProps()
Dim v, i&, d%
With Range("a1")
On Error Resume Next
With .Validation
If .Type = 3 Then v = Evaluate(.Formula1)
End With
d = UBound(v, 2)
If d = 1 Then v = Application.Transpose(v)
For i = LBound(v) To UBound(v)
If Len(v(i)) > 0 Then
.Value = v(i)
Call PrintMe
End If
Next
End With
End Sub

Sub PrintMe()
MsgBox Range("A1")
End Sub





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Co-op Bank wrote :

> Sorry its from a validation drop down box. cheers
>
> "Tom Ogilvy" wrote:
>
> > Is the dropdown box from an autofilter? If so, there is no way to
> > access that list. You can build a list you can loop through by
> > using the Advanced filter with the unique option.
> >
> > If it is from data validation, then just use the source data you
> > used to populate the list.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Co-op Bank" <CoopBank@discussions.microsoft.com> wrote in message
> > news:B0D99C29-FF7A-4056-807D-B6C84055B4EA@microsoft.com...
> > > Hello,
> > >
> > > I have a frustrating problem, I have a dropdown box in Excel
> > > which holds around 1,000 numbers (cost centres). When I select a
> > > number the values on
> > the
> > > spreadsheet change to those respective to the selected cost
> > > centre (i.e. sales £, volume).
> > >
> > > What I would like is a macro to loop through every cost centre in
> > > the dropdown box and print for all 1,000 selections in the
> > > dropdown. Is this possible? Otherwise i'd have to go through them
> > > all one by one to print
> > them
> > > off.
> > >
> > > Any help much appreciated. Regards Brian Taylor, Co-op Bank,
> > > Manchester,
> > UK
> >
> >
> >