How do you Print with a loop?

[rohrl77]

I have a drop downlist (data validation) that changes my report data. I want to use a 'loop' to print the 15 odd reports automatically without having to click through each combination. Any help is appreciated. Thanks!

[Jim Thomlinson]

Here is some code for you. You will need to change a couple of things to make
it work. I have commented the code where it needs to be changed. For debug
purposes it is set to printpreview instead of print. Just change the
commenting of the Printout line...

Sub PrintList()
Dim wks As Worksheet
Dim rngDropDown As Range
Dim rngList As Range
Dim rngCurrent As Range

Set wks = Sheets("Sheet1") 'Change this
Set rngDropDown = wks.Range("A1") 'Change this
Set rngList = Range(rngDropDown.Validation.Formula1)

For Each rngCurrent In rngList
rngDropDown.Value = rngCurrent.Value
'wks.PrintOut
wks.PrintPreview
Next rngCurrent

End Sub
--
HTH...

Jim Thomlinson


"rohrl77" wrote:

>
> I have a drop downlist (data validation) that changes my report data. I
> want to use a 'loop' to print the 15 odd reports automatically without
> having to click through each combination. Any help is appreciated.
> Thanks!
>
>
> --
> rohrl77
> ------------------------------------------------------------------------
> rohrl77's Profile: http://www.excelforum.com/member.php...o&userid=24248
> View this thread: http://www.excelforum.com/showthread...hreadid=378538
>
>

[Bernie Deitrick]

If your list is from a range or a named range, then you simply use code like

Dim myCell As Range
For Each myCell In Range("DataValidationListRange")
Range("CellwithDropdown").Value = myCell.Value
Application.CalculateFull
ActiveSheet.PrintOut
Next myCell


HTH,
Bernie
MS Excel MVP


"rohrl77" <[email protected]> wrote in
message news:[email protected]...
>
> I have a drop downlist (data validation) that changes my report data. I
> want to use a 'loop' to print the 15 odd reports automatically without
> having to click through each combination. Any help is appreciated.
> Thanks!
>
>
> --
> rohrl77
> ------------------------------------------------------------------------
> rohrl77's Profile:
http://www.excelforum.com/member.php...o&userid=24248
> View this thread: http://www.excelforum.com/showthread...hreadid=378538
>

[rohrl77]

Bernie & Jim,

Thank you for your help! Both codes work well for my problem!
Cheers,
Lukas