Print specific worksheets to PDF with pre-defined path & filename

[sfran]

Hello all,

I am trying to print to a pdf specific worksheets to a predefined filename (from cell value) and path. When I run this, the correct worksheets are selected however I still get the popup window to enter the filename. I would like this to save the selected worksheets to pdf without having to enter any details manually. Any guidance will be greatly appreciated.

Sub print()

Dim FName As String
Dim FPath As String

FName = Sheets("Project Description").Range("L3")
FPath = "C:\Effort Assessments\" & FName & ".pdf"

On Error GoTo ErrMsg

Sheets(Array("Concept Definition", "Design & Architecture", _
"Component Development", "Integration", "Validation", "Feature Owners", _
"Product Management")).Select
Sheets("Concept Definition").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False, prtofilename:=FPath

Exit Sub

ErrMsg:
MsgBox ("Group reports not created. Generate group reports and try again."), , "Export Error"

End Sub

 

[John_w]

When I run this, the correct worksheets are selected however I still get the popup window to enter the filename. I would like this to save the selected worksheets to pdf without having to enter any details manually.

The problem is caused by the PrintOut method, which would prompt for a file name if the default printer is a PDF 'printer'.

The solution is to replace the PrintOut line with:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FPath

which saves the active sheets as a PDF.

 

[sfran]

Thanks John_w..
I've commented out the PrintOut line and replaced it with yours (see below), however the sheets gets selected, but then jumps to the error message and does not create the pdf.

Sub printer()

Dim FName As String
Dim FPath As String

FName = Sheets("Project Description").Range("L3")

FPath = "C:\Effort Assessments\" & FName & ".pdf"


On Error GoTo ErrMsg


Sheets(Array("Architecture", "Product Management", _
"Component Dev & Int", "Validation", "ATO Scrum Masters & Proj Mgt")).Select
Sheets("Architecture").Activate

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FPath

' ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
' IgnorePrintAreas:=False, prtofilename:=FPath


Exit Sub

ErrMsg:
MsgBox ("Group reports not created. Generate group reports and try again."), , "Export Error"

End Sub

 

[jolivanes]

Have you checked the spelling of all the references like Sheet names? No leading or trailing spaces?

 

[jolivanes]

In your first post you mentioned that the right sheets are selected so that should take care of the post #4 suggestion.
When I ran the code from your last post, post #3, the macro worked as advertised after putting the double quotes back in around the Component sheet name.
When copying the code from post #3, the double quotes around "Component Dev & Int" disappeared.

 

[John_w]

I've commented out the PrintOut line and replaced it with yours (see below), however the sheets gets selected, but then jumps to the error message and does not create the pdf.

Comment out On Error GoTo ErrMsg because that's suppressing the real error and tell us the error message and on which line it occurs.