Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code needed to AUTOMATE VALIDATION selections and then PRINT EACHNAME dynamically
I have searched the blogs and cannot seem to locate anything related
to my challenge. I know you guys are gurus when It comes to things like this. I have a worksheet that tracks employee productivity for each cost center with a region. My excel worksheet contains three validation dropdowns that use the "indirect" function with various named ranges that link these dropdowns . So for instance lets say that the first dropdown relates to a region, the second relates to a cost center (there can be many cost centers in a region), and the third is a worker in that cost center (there can be many workers in a cost center). So, lets say that I select Northwest Region à 10001 (Cost Center) à and Jim Stu, John Doe, Bob Doe, and Jane Done can all be selected from this cost center individually which changes the productivity data for each worker. Drop Down 1 (region) -- Drop Down 2 (cost center) - Drop Down 3 (Worker) Range1 = Region Range2 = (indirect function from cell in Range 1) Range3 = (indirect function from cell in Range 2) What I would like to do is automate the printing process of the workers within a selected cost center using these already defined ranges. So for example in the example above I want to print all four names dynamically if some one decides that they would like to print all of the workers in ANY cost center. I am looking for the VBA code for this endeavor. Is this feat possible without having to select each and every worker within that cost center? Also, to take it a step further, is it possible to select all workers from every cost center and print all cost centers at once? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code needed to AUTOMATE VALIDATION selections and then PRINT E
Hi Robert;
Yes it is definetly possible, my question is can you do VBA? If yes then you just put the different key sources (employees, regions ....) into nested loops and go get the informaton. You can place the values for each report into a seperate spread sheet, format it and then print or if the employees have email address you could have Excel email them their report. The request is fairly complex and if you cannot do the VBA stuff is probalby a consulting job. I hope this helps, "robert R" wrote: I have searched the blogs and cannot seem to locate anything related to my challenge. I know you guys are gurus when It comes to things like this. I have a worksheet that tracks employee productivity for each cost center with a region. My excel worksheet contains three validation dropdowns that use the "indirect" function with various named ranges that link these dropdowns . So for instance lets say that the first dropdown relates to a region, the second relates to a cost center (there can be many cost centers in a region), and the third is a worker in that cost center (there can be many workers in a cost center). So, lets say that I select Northwest Region Ã* 10001 (Cost Center) Ã* and Jim Stu, John Doe, Bob Doe, and Jane Done can all be selected from this cost center individually which changes the productivity data for each worker. Drop Down 1 (region) -- Drop Down 2 (cost center) - Drop Down 3 (Worker) Range1 = Region Range2 = (indirect function from cell in Range 1) Range3 = (indirect function from cell in Range 2) What I would like to do is automate the printing process of the workers within a selected cost center using these already defined ranges. So for example in the example above I want to print all four names dynamically if some one decides that they would like to print all of the workers in ANY cost center. I am looking for the VBA code for this endeavor. Is this feat possible without having to select each and every worker within that cost center? Also, to take it a step further, is it possible to select all workers from every cost center and print all cost centers at once? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code needed to AUTOMATE VALIDATION selections and then PRINTE
Thanks for the reponse DownthePaint. I am very knowledgable of vba and
was hoping i could get code to automate this process. I simply want the code to go through the Worker Dropdown and print all of those names associated with the cost center range. These ranges are listed on one worksheet that this is related to. To create all 100+ of the ranges i simply used the Insert-- Names -- Create technique which creates a separate range for each cost center which has several workers under it. So for instance, if i have cost center "1234" selected, the indirect function is linked to a separate worksheet tab which pulls all worker names for this range which happens to be named "1234" and has associated names in the worker dropdown to this range. My first request was to just merely select each worker which returns his/her own data perfomance on each page and print all of these pages by cost center selected. My second request was to have the macro go through all cost center/worker combinations and print all workers pages by each cost center range . I have some code that i have started (Thanks to Tom Oglivy) tinkering around with but it does not seem to work with all of these ranges defined ranges. What am i missing? Sub worker_by_costcenter() Dim Cell As Range For Each Cell In Range("NamedRangeName") Range("b3").Value = Cell.Value ActiveSheet.PrintOut Next Cell End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code needed to AUTOMATE VALIDATION selections and then PRINTE
I was able to figure it out through no help of any of you so called
GURUS...It seems that you guys will only answer easy posts and not the more difficult ones which would really validate your knowledge as an excel programmer. Here is the code which selects from a validation list and prints each page for every selection dynamically (or automatically) with a simple loop: Sub Validate_Print_Adjusters() 'This macro was created by Robert R Dim rng As Range, cell As Range Set rng = Evaluate(ActiveSheet.Range("F3").Validation.Formul a1) For Each cell In rng ActiveSheet.Range("F3").Value = cell.Value If cell.Value = "" Then Exit Sub ActiveSheet.PrintOut Next End Sub Robert R... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Amend the DV0022 - Update Validation Selections code for more lists | Excel Worksheet Functions | |||
Print routine needed for code | Excel Worksheet Functions | |||
changing selections dynamically | Excel Discussion (Misc queries) | |||
Code needed to print multiple workbooks | Excel Programming | |||
Code to produce color font in data validation selections | Excel Programming |