Creating multiple PDF files

[Domski]

Hi,

I'm in the middle of a very dull and laborious job and am hoping there may be a solution out there to assist me with it.

I have a workbook with a number of worksheets showing different graphs and statistics.

These are controlled by a front sheet on which I enter the month and department that are to be displayed.

I then create individual PDF files using Acrobat Distiller by changing the month and department, printing, changing, printing etc I've recorded a few macros so as I can click buttons to print certain sheets but I still have to manually change to variables and enter a file name each time.

What I was hoping was that I could create a sheet with the variables, sheets to be printed and file names, hit a button and leave Excel to it.

Has anyone come across a solution to this? I've tried searching the board but to no avail.

Cheers,

Dom


PS My VBA and programming skills are very much in their infancy.

 

[Norie]

Dom

Can you post what code you already have?

 

[Domski]

Hi Norie,

This is about as far as I've got with any successful code:

Sub Print_Sheets()
' Application.ScreenUpdating = False
Sheets(Array("Sheet1", "Sheet2")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Front Sheet").Select
' Application.ScreenUpdating = True
End Sub

This works with multiple sheets (usually) but you have to print once manually to Acrobat and specify the file name when prompted.

I tried to take a different approach with:

Sub Print_Sheets2()
Sheets(Array("Sheet1", "Sheet2")).Select
Application.ActivePrinter = "Acrobat Distiller on C:\WINDOWS\All Users\Desktop\*.pdf"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Acrobat Distiller on C:\WINDOWS\All Users\Desktop\*.pdf", Collate:=True
End Sub

This correctly prints to Acrobat without prompting but insists on a different file for each worksheet and whenever I try to add any code to specify a file name it just freeks completely. I guess this is because it is Distiller that is asking for the filename and not Excel.

I did find a post at [/url]http://www.mrexcel.com/board2/viewtopic.php?t=119332&highlight=pdf but it involves third party softwa...ig gob can chew here. Cheers, Dom :wink: