Use VBA to print selection of worksheets to single pdf and save in the same folder as original file.

DaysoutDigital

New Member
Joined
Sep 7, 2014
Messages
5
OK, so I have an excel document with over 20 worksheets in it. When the user gets to sheet 18 there is a button which I want them to be able to click and it will create a single PDF of worksheets - Page 18, Page 21, Page 22, Page 23, Page 24, Page 25.
The pdf needs to be saved into the save location as the original worksheet.
I have got this code so far which creates a PDF of the current sheet, and I like the fact that it opens the SaveAs window so the user can change this if they want.

Sub PDFActiveSheet()
Dim ws As Worksheet
Dim strPath As String
Dim myFile As Variant
Dim strFile As String
On Error GoTo errHandler


Set ws = ActiveSheet


'enter name and select folder for file
' start in current workbook folder
strFile = Replace(Replace(ws.Name, " ", ""), ".", "_") _
& "_" _
& Format(Now(), "yyyymmdd\_hhmm") _
& ".pdf"
strFile = ThisWorkbook.Path & "\" & strFile


myFile = Application.GetSaveAsFilename _
(InitialFileName:=strFile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and FileName to save")


If myFile <> "False" Then
ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=myFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False


MsgBox "PDF file has been created."
End If


exitHandler:
Exit Sub
errHandler:
MsgBox "Could not create PDF file"
Resume exitHandler
End Sub


Basically I would like to use this code but be able to modify it to print the sheets highlighted above.

Thank you for you help.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

DaysoutDigital

New Member
Joined
Sep 7, 2014
Messages
5
And it would be nice if the pdf used the same name as the excel file rather than created a new name...

Thank you
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,914
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top