Macro to change validation list and print
 

Hi,

I have a worksheet with graphs and tables on it which are all based on a
validation list (the drop down menu variety) with about a dozen categories.
Obviously when i change the selected category, all the graphs and tables
change too.

I would like to create a macro that will print one copy of the worksheet for
each category on the validation list, rather than having to manually change
the list and then print the worksheet a dozen times.

Any suggestions?

Andy

Did you put that list of 12 on a worksheet or did you enter it in the
data|validation dialog directly?

If you put it in a list on a worksheet, then something like this could work:

Option Explicit
Sub testme()

Dim myCellWithValidation As Range
Dim myValidationRng As Range
Dim myCell As Range

Set myCellWithValidation = Worksheets("sheet1").Range("a1")
Set myValidationRng = Worksheets("sheet2").Range("myList")

For Each myCell In myValidationRng.Cells
myCellWithValidation.Value = myCell.Value
Application.Calculate
myCellWithValidation.Parent.PrintOut preview:=True
Next myCell

myCellWithValidation.ClearContents

End Sub


I used preview:=true to save paper.



Andy wrote:

Hi,

I have a worksheet with graphs and tables on it which are all based on a
validation list (the drop down menu variety) with about a dozen categories.
Obviously when i change the selected category, all the graphs and tables
change too.

I would like to create a macro that will print one copy of the worksheet for
each category on the validation list, rather than having to manually change
the list and then print the worksheet a dozen times.

Any suggestions?

Andy

--

Dave Peterson