Print to pdf and save without prompts


G

Guest

I have a macro which takes a selection of each page in a workbook. (specific
to each page) This is then printed to pdf and at the moment I have to save
each filename as I go along. Which is obviously time consuming. Is there a
way of naming these without a prompt coming up automatically and saving it in
a new folder in a given location?

Cheers in advance

Jeff
 
Ad

Advertisements

G

Guest

This is my somewhat untidy code so far....

Sheets("Total").Select
Range("A1:M40").Select
Application.ActivePrinter = "Adobe PDF on Ne00:"
Selection.PrintOut Copies:=1, ActivePrinter:="Adobe PDF on Ne00:",
Collate _
:=True
Range("N4:AC38").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Sanction").Select
ActiveSheet.ChartObjects("Chart 2").Activate
Application.Run "OnActivate"
Windows("FSPI Main.xls:1").LargeScroll ToRight:=-1
ActiveWindow.Visible = False
Windows("FSPI Main.xls:1").Activate
Range("A1:M40").Select
Application.Run "OnActivate"
Selection.PrintOut Copies:=1, Collate:=True
Range("N1:AC38").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Burglary").Select
Application.Run "OnActivate"
Range("A1:M41").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("N1:AC39").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Violent").Select
Application.Run "OnActivate"
Range("A1:M39").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("N1:AC40").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("VCPP").Select
Application.Run "OnActivate"
Range("A1:M41").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.PrintOut Copies:=1, Collate:=True
Range("N7:AC41").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Robbery_Source").Select
Application.Run "OnActivate"
Sheets("Robbery").Select
Application.Run "OnActivate"
Range("A1:M40").Select
Range("M40").Activate
Selection.Copy
Application.CutCopyMode = False
Selection.PrintOut Copies:=1, Collate:=True
Range("N3:AC37").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("X39").Select
Sheets("Vehicle_Source").Select
Application.Run "OnActivate"
Sheets("Vehicle").Select
Application.Run "OnActivate"
Range("A1:M41").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("N9").Select
ActiveWindow.SmallScroll Down:=-12
Range("N2:AC40").Select
ActiveWindow.SmallScroll Down:=-12
Selection.PrintOut Copies:=1, Collate:=True
Sheets("ClassA_Source").Select
Application.Run "OnActivate"
Sheets("ClassA").Select
Application.Run "OnActivate"
Range("A1:M41").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Criminal_Damage_Source").Select
Application.Run "OnActivate"
Sheets("Criminal_Damage").Select
Application.Run "OnActivate"
Range("A1:M40").Select
ActiveWindow.SmallScroll Down:=6
Range("A1:M41").Select
Range("M41").Activate
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Criminal_Damage").Select
Range("N3:AC39").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Incidents source").Select
Application.Run "OnActivate"
Sheets("Incidents").Select
Application.Run "OnActivate"
Range("A1:M45").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("N2:AD41").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("CHC Presentation").Select
Application.Run "OnActivate"
Range("A1:M63").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("CHC + CRIB").Select
Application.Run "OnActivate"
Sheets("CHCsource").Select
Application.Run "OnActivate"
Sheets("Emergency Calls").Select
Application.Run "OnActivate"
Range("A1:M39").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("N4:AD39").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("999 History").Select
Application.Run "OnActivate"
Sheets("Searches Presentation").Select
Application.Run "OnActivate"
Range("A1:M47").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("N3:AD40").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("PACE Searches History").Select
Application.Run "OnActivate"
Sheets("Stops Presentation").Select
Application.Run "OnActivate"
Range("A1:M47").Select
Selection.PrintOut Copies:=1, Collate:=True
ActiveWindow.SmallScroll Down:=-21
Range("N9:AD43").Select
Range("AD43").Activate
Selection.PrintOut Copies:=1, Collate:=True
Sheets("PACE Stops History").Select
Application.Run "OnActivate"
ActiveWindow.SmallScroll ToRight:=30
ActiveWindow.SmallScroll Down:=0
Sheets("Adult Casualties").Select
Application.Run "OnActivate"
Range("A1:M46").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("N1:AD47").Select
ActiveWindow.SmallScroll Down:=0
Range("N2:AD48").Select
ActiveWindow.SmallScroll Down:=-6
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Adult RTC Histories").Select
Application.Run "OnActivate"
Sheets("Child Casualties").Select
ActiveSheet.ChartObjects("Chart 1").Activate
Application.Run "OnActivate"
ActiveWindow.Visible = False
Windows("FSPI Main.xls:1").Activate
Range("A1:M46").Select
Application.Run "OnActivate"
Selection.PrintOut Copies:=1, Collate:=True
Range("N3:AC46").Select
Range("AC46").Activate
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Complaints").Select
ActiveSheet.ChartObjects("Chart 1").Activate
Application.Run "OnActivate"
ActiveWindow.Visible = False
Windows("FSPI Main.xls:1").Activate
Range("A1:M51").Select
Application.Run "OnActivate"
Selection.PrintOut Copies:=1, Collate:=True
ActiveWindow.SmallScroll Down:=-30
Range("N1:AB39").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Complaints Histories").Select
Application.Run "OnActivate"
Sheets("Priorities").Select
Application.Run "OnActivate"
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Update Procedure").Select
Application.Run "OnActivate"
End Sub
 
Ad

Advertisements

G

Guest

Hey Jeff,
I found this discussion between RC and Darcy from July last year in Excel
General Questions with the header “Macro printing to a file†and I managed to
solve the PDF saving problem. The original suggestion was to save numerous
files, I’m saving only one at a time. I’m running Excel 2003 and Acrobat 6.0.
The trick to get rid of the saving prompt was to change the printer
properties in the Control Panel – Printers and Faxes – Adobe PDF – Properties
– Printing defaults.
You cannot set these properties permanently within the properties in Excel,
as soon you have reset to your Standard Printer the options are selected
again.
Clear the selections for “Prompt for Adobe PDF filename†AND “Do not send
fonts to “Adobe PDFâ€â€. Both are important, the latter for the driver to make
the Postscript files that’ll be deleted after conversion to PDF-files.
I have a shorter solution for 1 sheet only but if you want it I can post it
too!
Best regards
Mats
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top