VBA Code needed to AUTOMATE VALIDATION selections and then PRINT EACHNAME dynamically

[robert R]

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?

 

[DownThePaint]

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]

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

 

[robert R]

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.Formula1)
For Each cell In rng
ActiveSheet.Range("F3").Value = cell.Value
If cell.Value = "" Then Exit Sub

ActiveSheet.PrintOut

Next
End Sub

Robert R...