Excel printing to pdf using vba

QuestionEdit

I need to establish a routine to be able to print various sheets in various combinations as a series of different reports, all of which would ideally be printed to PDF for electronic distibution. I may need an updated Adobe product to enable that but would like to get this figured out soon. I'm also willing to pay you separately as a consultant as what we're looking for may be more than we can get with a simple question.

I'm thinking of something like what appears in the attached image, where I can establish various sheets and ranges that may appear in some reports, not others.

Looking forward to your help.

Thanks, Lary

AnswerEdit

Lary

here is some rough pseudo code to represent looping through your list. You made no explanation of the special qualifiers you have on the right, so I haven't included them in the example.

Dim r as Range, cell as Range set r = Range("B3",Range("B3").End(xldown)) for each cell in r

 set sh = Worksheets(cell)
 set r1 = sh.Range(cell.offset(0,1))
 sh.pageSetup.PrintArea = r1.Address(1,1,xlA1,True)
 sh.Printout

Next

If you are using xl2007, then there is an addin available from Microsoft which supports printing to PDF directly. If not, then you would need something to handle the conversion to a PDF file. This could be Adobe or some third party application.

MVP Ken Puls has a lot of documentation on his site about using a specific 3rd party application for printing PDF and also for the newAddin for 2007. You can find his site here

http://www.excelguru.ca/

You might contact him if you want to hire a consultant. (He has a job, but I believe he also consults).

I personally don't consult.