Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! - Printing a sheet with values populated from dropdown box
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! - Printing a sheet with values populated from dropdown box
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! - Printing a sheet with values populated from dropdown b
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! - Printing a sheet with values populated from dropdown b
So work from the list that you use as the source of the validation dropdown.
-- Regards, Tom Ogilvy "Co-op Bank" wrote in message ... 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" wrote in message ... 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 them off. Any help much appreciated. Regards Brian Taylor, Co-op Bank, Manchester, UK |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! - Printing a sheet with values populated from dropdown b
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I insert a work sheet populated with data into another? | Excel Worksheet Functions | |||
Having data automatically populated in a different sheet | Excel Worksheet Functions | |||
populating DropDown with Values Of a Column in another Sheet. | Excel Discussion (Misc queries) | |||
Copy Values to Below last Populated Cell Q | Excel Programming | |||
Printing sheets with populated fields | Excel Programming |