Printing data validation scenarios

[SJC]

I have created a workbook which reports facility data trends. I created a
standard report to report this data for each facility. To create each
facility's scenario, I used a data validation list with a drop box of
facility names to create a standard report for each facility. Obviously only
one report may be seen and printed at one time. Is it possible to print out
all scenarios in the validation list, or do I need to print each report one
at a time. Thanks in advance for any help.

 

[Ron de Bruin]

Hi SJC

You can do it with a macro.
Do you want that ?

Do you have a list with facility names on a sheet and use that
range for the Data Validation list ?

 

[SJC]

A macro would do fine--I guess I am just not sure how to set it up. I have a
list of facility numbers in row K that I used the for the data validation
list, which of course is on the same spreadsheet as the report that I want to
print. Any clue on how I would get started?

 

[Ron de Bruin]

I will answer you when I come home from work

 

[Ron de Bruin]

Hi

Try this with D1 as data validation cell that update your other data
I use a sheet with the name Sheet1

Delete preview:=True when it is working like you want

Sub test()
With Sheets("Sheet1")
For Each cell In .Range("K1:K5")
'D1 is the data validation cell
.Range("D1").Value = cell.Value
.PrintOut preview:=True
Next cell
End With
End Sub

 

[SJC]

Thanks for the code. Here is what I tried:

Sub test()
With Sheets("Report")
For Each cell In .Range("J1:J5")
.Range("C6").Value = cell.Value
.PrintOut preview:=True
Next cell
End With
End Sub

I ran the sub, and it did not pull the data for all of the reports as it was
supposed to. As background info, I have all of the data in another
spreadsheet within the workbook named 'Data', and I have it linked to the
'Report' spreadsheet. Then obviously the report changes by what name is
selected in the data validation list. Any clues on what I am doing wrong?

 

[Ron de Bruin]

I was thinking that you use Vlookup formulas in your sheet with as lookup value
the Data Validation cell.

how do you update the cells when you change C6

BTWI see J in the code now ?

 

[SJC]

I'll tell you everything that I have in hopes to answer your question, as I
am still kind of new at this. In my workbook, I have two spreadsheets. My
'Data' sheet holds all of my data. I then have a 'Report' sheet which
produces a one page report from my data which includes a table and two
charts. My hope for this was to create a standard report that would
automatically update for each of my 300 facilities. So on my report
worksheet, I have used OFFSET formulas to retrieve the data from the 'Data'
worksheet, and a data validation listbox to choose which facility to display.
On the 'report' worksheet, column J is my reference list for the data
validation list. This list obviously was used to create the data validation
listbox, and to compile this list, I just pasted links from the 'Data'
spreadsheet. So now I am trying to create a macro which will go through my
data validation list and print one report for each facility. When I view
each report on the spreadsheet using the data validation listbox, the report
appears fine. However, when I run the sub to print, it produces one report
for each facility, but in many reports, no data is present. In other
reports, it appears fine. I hope I have not been too confusing, any clues on
how to fix this?

 

[Ron de Bruin]

Hi SJC

Can you send me the workbook private then I will look at it tomorrow

 

[SJC]

Hi Ron, thanks for the offer, but I would get into some pretty decent trouble
for sharing the file. Thanks for all of your help thus far.

 

[Dave Peterson]

What's the range that contains the data|Validation entries (on worksheet Data)?

I think you just want this:

Sub test()
With Sheets("Report")
For Each cell In worksheets("data").Range("datavalrangenamehere")
.Range("C6").Value = cell.Value
'I'd add
application.calculate 'just in case
.PrintOut preview:=True
Next cell
End With
End Sub

Maybe it's that simple change.

 

[SJC]

On the data spreadsheet, the facility number, of which I used to create the
validation list is in column A. However, on the report sheet, I have the
facility list in column J which was directly used to make the validation
list. So then, everything to do with the validation list occurs on the
report spreadsheet, only the actual data is on the data spreadsheet. The
validation list in cell C6 is on the report spreadsheet. If I am making any
kind of sense, what would be the best way to fix this? The code thus far
doesn't seem to work.

 

[Dave Peterson]

Ron guessed that your validation list was in K1:K5
For Each cell In .Range("K1:K5")

If it's in column J, change that address to what it should be
For Each cell In .Range("Jx:Jy")

(change x and y to the row numbers that apply.)

 

[SJC]

Yes, I had changed the range so that it was correct. I was able to run the
code, and it did produce a report for each facility. However, some
facilities did not have the data on the report. I did remember I put
together a sample file, but it does not appear that one can use attachments

 

[Ron de Bruin]

Hi SJC

Send it to me private and I look at it this weekend
My e-mail is on my site

--
Regards Ron de Bruin
http://www.rondebruin.nl

Yes, I had changed the range so that it was correct. I was able to run the
code, and it did produce a report for each facility. However, some
facilities did not have the data on the report. I did remember I put
together a sample file, but it does not appear that one can use attachments