Print Selected Sheets to User Defined Location as pdf

AggyRJ

New Member
Joined
Mar 29, 2013
Messages
14
I have a file that generates several worksheets which need to be archived daily. In order to save space, in the past I wrote a macro that would save these sheets as an .xps file. However, our IT department made the decision to upgrade to Windows 10, which does not support .xps files without jumping through hoops which they don't want to do.

Because of this, I need to modify the code to save as a .pdf instead, however I am having some issues doing this. The old code would just create a prompt where the user would choose where to save the file, and the default file type would be .xps. However all of the working codes that I have been able to research for pdfs save to a specific location, which I cannot do since this file is used by multiple departments across the country.

What I need is code that will create a user prompt where the user can save selected worksheets to destination they choose as a pdf. If possible I would like to add the ability to define the file name to today's date, which I have generating in the format "DD-Month-YYYY" in the "Instructions" worksheet in cell AA1. This way every file is saved with the same naming convention, but the user still gets to choose where they will save it. This is optional as the old code did not do this but it is something I would like to add.

I will paste the old code below for reference.

Sub SaveAsXPS()

Sheets("Instructions").Select

Sheets(Array("SM0", "SM1", "SM2", "MSL by WA", "GF Planning")).Select

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Microsoft XPS Document Writer", Collate:=True

End Sub
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,413
Try changing "Microsoft XPS Document Writer" to "Microsoft Print to PDF".
 

AggyRJ

New Member
Joined
Mar 29, 2013
Messages
14
Did not work - just printed to my default printer. I need the file to actually save as a pdf in a destination that the user chooses.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,708
Messages
5,524,428
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top